I am trying to figure out a formula that will count the number of people that meet multiple criteria. There are 3 criteria that I am trying to meet, First Column A has the persons Work Class, Column B has the persons Company Name, and then Columns K thru N has the persons hours worked for the Month. The Formula I am trying to create is the count the number of people with the same work class, same company and if they have worked more then 1 hour. Does anyone have any suggestions? Thank you in advance.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Countif Multiple Criteria
- Thread starter Rob #4
- Start date
anglais428
Well-known Member
- Joined
- Nov 23, 2009
- Messages
- 634
- Office Version
- 2016
- Platform
- Windows
use the countifs function if you have Excel 07+
=Countifs(range1,criteria1,range2,criteria2....)
=Countifs(range1,criteria1,range2,criteria2....)
Upvote
0
I have determined SumProduct will work. I have the formula reconizing the first 2 criteria, now the last part of it I am trying to figure out is how to include the hours worked. I have the hours for the month on 4 columns, by week, Columns K, L, M, N. I want to finish the formula by saying that if the number in any of these columns is >1, I want to count them. That might be confusing, but if anyone has any ideas, please let me know. Thanks
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Control+shift+enter, not just enter:
=(SUM(IF($A$2:$A$100=Class,IF($B$2:$B$100=Company,$K2:$N$100)))>1)+0
Does this satisfy? Note that you need to specify Class and Company.
Upvote
0
This got me past 2 criteria:
=SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Company"),--('Resident Worker Hour Report'!$A$19:$A$400="Class"))
But now in Columns K thru ??????, I have each week of the year, listing workers hours for each week. Now that I have the number of people from each company and class, I need to know the number of people who worked hours in January, February, etc. So January is Columns K thru N, February is columns O thru R. I need to continue this formula if someones hours are more the 1 I want to count them.
The formula I have above returns the number of people working for a company and each class as 7. Of that 7 maybe only 2 worked hours in January and maybe only 4 worked in February.
Hopefully this shows a little more of what I am trying to do. Thanks
=SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Company"),--('Resident Worker Hour Report'!$A$19:$A$400="Class"))
But now in Columns K thru ??????, I have each week of the year, listing workers hours for each week. Now that I have the number of people from each company and class, I need to know the number of people who worked hours in January, February, etc. So January is Columns K thru N, February is columns O thru R. I need to continue this formula if someones hours are more the 1 I want to count them.
The formula I have above returns the number of people working for a company and each class as 7. Of that 7 maybe only 2 worked hours in January and maybe only 4 worked in February.
Hopefully this shows a little more of what I am trying to do. Thanks
Upvote
0
