Struggling with COUNTIFS in Excel: How Do I Manually Input Criteria for Headcount?

Hello Members,

Can anyone help me with the COUNTIFS function for headcount in this file? I have to input the criteria manually as I cannot reference it to column D. The formula should work from cell 233E to 250E. For further clarification, please refer to cells 233F to 250F to understand what I am trying to explain.

Any help will be greatly appreciated.

Regards,
Navin

Hello Navin,

I did my best, but it's not working because the department codes mentioned in the sheet are in the format of dates. To solve this issue, you will need to write a macro to obtain the count.

Hi,

What I want is, instead of typing criteria in the formula, can't I refer to specific cells so that I don't have to type "dept code" in each cell? In an Excel spreadsheet, there are examples of both situations: in one, I typed the "dept codes" and it worked, and in the other, I have given cell references, but it's not working - the result is zero.

I think this explanation should be all right. Thanks for your efforts.

Regards,
Navin

Hi Manikanth,

Thanks for trying. In fact, this is what one of my Excel expert friends told me. But I thought to give it a try as there is always a "Sava Ser on Ser." Thanks for your help.

Check the summary sheet in the workbook and see if it is what you want. I'm not sure if I got the problem statement correctly.

Could you please explain in detail what you want as a result in that column.

Hope you will find the solution if you describe in detail.

Hi Navin,

It's the same as what you have done in column E. If you want headcount department-wise, first, you need to extract department data from the "default cost account" column using the LEFT formula. I have attached your file with the formula.

Best regards,
Sheikh

Hello Members,

Can anyone help me with this file in the `COUNTIFS` function for headcount? I have to type in the criteria; I can't refer it to column D. The formula worked as in 233E to 250E. For your reference, please see 233F to 250F to understand what I am trying to explain.

Help will be appreciated.

Regards,
Navin

This is a custom function you are using, so this may not work on cell reference. I have used another `COUNTIF` which is giving the same results. Hopefully, it will solve your purpose.

Cheers

[QUOTE=nickkan;919718]

Hi,

You should understand that the Excel functions reduce your calculations and are error-free. The function COUNTIFS will work with large data. I don't think there is a solution for your query.

For more clarity, your sheet has a column "C" which is to be counted department-wise, right? So, you used the COUNTIFS command and got the number of employees in that particular department (i.e., '1-1-11'). If you refer to a specific cell and count, it will not work.

Hope it is clear.

Hi there,

You are a champ. This is exactly what I want, but I was using COUNTIFS instead of COUNTIF. The formula you used, COUNTIF(C:C,"*"&D233&"*"), went over my head. I know the end result is exactly what I want, and I will save this worksheet so I can refer to the function you used in the future. Can you help me understand why "*" is different from "*" which I have used?

Many thanks for your help, buddy.

Regards,
Navin

Hi Friends,

I need one more favor. I thought to post it separately but was sure I would be able to get a solution from one of you. Attached is another workbook in which I need some help.

Sheet "Acrobat to Excel" has tag numbers in column B and amounts in column C. I want to find a formula or a way to display the tag number and the following amount in the same row. For example, 7487981- and $3 in cells D3 and E3.

Currently, I copy column B and C, paste them into a different sheet, sort the tags in sequence, match them with the respective amounts, and then copy and paste them again. This method works fine, but it feels like there is an additional step that I wish to avoid.

I have attached the sheet herewith. Please let me know if you have any questions.

Thank you in advance.

Regards,
Navin

Please check if this is what you want. It is not working in your case because the criteria you are looking for (e.g., d233) is not an exact match in column C.

Hi Nickkan,

I think I tried the best solution for your Excel problem. Please check it out and reply if it is the correct solution.

Instead of "dept," you can retrieve the number of employees by using the cell reference of the code only. Why do you have to create one more column for the department? I am exploring other options, and once I find one, I will reply.

Hello Niccken, I found a solution for your problem. Please see it.

When you use a cell reference and apply the * option, it should be accompanied by &, as that's how Excel functions. If you are not using a cell reference, then you can apply the * option without &.

Hope this clarifies your doubt.

Cheers

When I opened this file, it shows me binary digits like characters. I really cannot read this file, so I cannot help as the file contains proper information. Cheers!

Dear,

Please check the attached file. I hope that your problem has been solved. If you need any clarification, please revert back.

Best Regards,
Alam

Dear all,

Can anyone help me with how to calculate PF returns and the formalities for submission of the same?

Regards,
Mukesh