Count if any of the following exist

[mkicetea]

Hello!
This is my first post on this forum, not too familiar with it, if there's an answer for my question on a thread that I might have missed, please post.

Question:
I have set up a calendar on my spreadsheets with names and other info under each day. I would like to count the number of employees that worked for that one month. How do I get excel to count if any of the names (from a list) appear on that certain month?

I have tried doing the countif+countif+countif... formula but I have a long list of employees (20 names)

Thanks.

 

[Redwolfx]

If I was working and worked 15 days, should my name be counted once or 15 times?

 

[mkicetea]

If I was working and worked 15 days, should my name be counted once or 15 times?

Hi Redwolfx,


Your name would could 15 times. and lets say if I had my name on for 10 times, the total count would be 25 and so on for the rest of the employee list.

Thanks,
M

 

[Redwolfx]

You could do one of a few things. You could write a really long formula in a single Cell.

You could create a small table with each of the employees names, then do a single countif for each, then sume the totals.

 

[mkicetea]

Ok, I have already done that, but is there an easier way? like count all the names on this list if any appear in this range?

 

[shg]

Maybe

=sum(countif(calendarRange, nameRange))

 

[mkicetea]

Maybe

=sum(countif(calendarRange, nameRange))

I'm getting a 0, I think it's telling excel to find all the cells that contains all of the criteria contained in nameRange. So basically every cell that contains all of these names, that's not what im after. I want a count of any of the names contained in a certain range.
Thanks

 

[shg]

Sorry -- you need to confirm the formula with Ctrl+Shift+Enter instead of just Enter.

Or use =sumproduct(countif(calendarRange, nameRange))

 

[mkicetea]

Sorry -- you need to confirm the formula with Ctrl+Shift+Enter instead of just Enter.

Or use =sumproduct(countif(calendarRange, nameRange))

Both worked like charms. Thanks and have a great weekend!!
M

 

[shg]

You're welcome.