Excel Count values from column C if criteria in column A and B are met

New Contributor

Hi I will try to explain this as best as I can. 

 

I am trying to update a work spreadsheet. Previously the number 1 was used to identify if someone had attended a class yet it would be easier if we could instead list the date the class was taken. I've been able to then =COUNT the column to get a total number for each class type.

 

The issue is that we also need to know how many participants in each class (column C) meet criteria in column A (ethnicity) and column B (gender). The gender and ethnicity columns are numbers (either 1 or 2). 

 

Is there a function to count instances in column C (dates) based on if that cell aligns with criteria in column A (1 for hispanic 2 for not hispanic) and column B (1 for male 2 for female)?

 

The current function before I changed column C to dates was {=SUM(($E$5:$E$34=1)*($G$5:$G$34=1)*(O$5:O$34=1))} but now that I've changed column C to dates instead of the number 1 that formula no longer auto updates and instead returns the #VALUE! error. 

 

I've tried =IF((COUNTIFS($E$5:$E$34,"1",G5:G34,"2")),(L35),"0") but this only works if everyone is either a 1 or 2 in column A and B. I think it's because of the L35 but I'm not sure how to set the [value if true] to reflect what I need. 

2 Replies
You're on the right track. This should work
=COUNTIFS($E$5:$E$34,1,$G$5:$G$34,1,O$5:O$34,">0")
alternatively:
=SUMPRODUCT(($E$5:$E$34=1)*($G$5:$G$34=1)*(O$5:O$34>0))

@mtarler 

Thank you, I was able to get it to work with =(COUNTIFS($E$5:$E$34,"1",$G$5:$G$34,"1",J5:J34,"<>"))