Count if any of the following exist

mkicetea

New Member
Joined
Jan 31, 2014
Messages
5
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
If I was working and worked 15 days, should my name be counted once or 15 times?
 

mkicetea

New Member
Joined
Jan 31, 2014
Messages
5
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

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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

New Member
Joined
Jan 31, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Maybe

=sum(countif(calendarRange, nameRange))
 

mkicetea

New Member
Joined
Jan 31, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Sorry -- you need to confirm the formula with Ctrl+Shift+Enter instead of just Enter.

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

mkicetea

New Member
Joined
Jan 31, 2014
Messages
5
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top