Excel help on countifs

manohargoudc · #11 22-10-2009, 07:37 PM

HI

You should understand that the excel functions reduce your calculation and are error free. the function countifs will work with large data.

I dont think there is a solution for your query.

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

Hope it is clear

nickkan · #12 23-10-2009, 06:40 AM

Hi there,

You are a CHAMP. This is what I exactly want. but I was using Countifs instead of coutif.

But still the formula use used COUNTIF(C:C,"*"&D233&"*"), went over my head.

I know end result is exactly what I want and I will save this worksheey, so I can refer the function u used in future.

But can you help me to understand why "*" is different to "* which I have used.

Many thanks for your help buddy.

Regards,
Navin

Quote:

Originally Posted by carora

this is 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 will solve your purpose.

Cheers

nickkan · #13 23-10-2009, 06:45 AM

Thanks to all of you for your help. I finally got what I was after.
Friends,many thanks once again.

Cheers,
Navin

nickkan · #14 23-10-2009, 07:14 AM

Hi Friends,

I need one more favour. I though to post it separate but was sure would be able to get solution from someone of you. Attached is another workbook in which I need some help.

Sheet "Acrobat to Excel" column b have tag nos and column c have amount. By any formula/way I want to get tag no and following amount in same row. For example 7487981- and $3 in cells D3 and E3.

Now I copy column b and c and paste it in different sheet and then sort tags as all are in sequence and go to constants for amount and then copy and paste it. It works allright but seems that there is an additional step which I wish to avoid.

Attached herewith is the sheet. Pl let me know if do you have any questions.

Thank you in advance.

Regards,
Navin

shindarocks · #15 23-10-2009, 10:21 AM

Please wait brother i am working on the same

VASAN · #16 23-10-2009, 12:12 PM

Pl chk if this is what u want. It is not working in ur case because, the criteria that u r looking (eg. d233) is not an exact match in column c

chella pandi · #17 23-10-2009, 12:13 PM

hi nickkan.,

i think i tried some best solution for your excel problem. please check it out and reply me...

if it is correct solution.

vmohan1978 · #18 23-10-2009, 03:18 PM

Instead of dept you can get no of employee by using cell reference of code only.
why you have to creat one more column Dept.
other option i am looking in to that once i find i will reply

chella pandi · #19 23-10-2009, 05:05 PM

Hello niccken i found some solution for your problem.

please see it.

Quote:

Originally Posted by nickkan

Hello Members,

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

Help will be appreciated.

Regards,
Navin

carora · #20 23-10-2009, 06:45 PM

When you use a cell refernce and use like * option then it should be used along with &, that`s how excel works.
If you are not using cell referecne then you can use like * option without &
Hope this clarify your doubt.

Cheers

Quote:

Originally Posted by nickkan

Hi there,

You are a CHAMP. This is what I exactly want. but I was using Countifs instead of coutif.

But still the formula use used COUNTIF(C:C,"*"&D233&"*"), went over my head.

I know end result is exactly what I want and I will save this worksheey, so I can refer the function u used in future.

But can you help me to understand why "*" is different to "* which I have used.

Many thanks for your help buddy.

Regards,
Navin