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

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

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

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))

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

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