Forum Discussion
How to COUNTIFS with mixed criteria
I am having some issues with using the COUNTIFS function. My spreadsheet has all the readers enrolled in my reading challenges for June, sorted by age. I need to be able to quickly calculate how many readers in each of my age groups logged reading during the month, but the Ages are messing me up. I used the following functions to successfully determine:
How many Avid readers aged 0-11 logged reading: =COUNTIFS(AVID!$A$2:$A$500,"<12",AVID!I2:$I$500,">0")
How many Avid readers aged 12-18 logged reading:
=COUNTIFS(AVID!$A$2:$A$500,">11.5",AVID!$A$2:$A$500,"<19",AVID!$I$2:$I$500,">0")
How many Casual readers aged 0 - 11 logged reading:
=COUNTIFS(CASUAL!$A$2:$A$500,"<12",CASUAL!$I$2:$I$500,">0")
How many Casual readers aged 12-18 logged reading:
=COUNTIFS(CASUAL!$A$2:$A$500,">11.5",CASUAL!$A$2:$A$500,"<19",CASUAL!$I$2:$I$500,">0")
note: I have not imported RTM reader data yet . . .
What is messing me up are the ages of my Adult readers. We collect readers ages up to age 20, but all other adult readers are listed as "21+". How do I amend the COUNTIFS formula to scrape "19," "20," and "21+" from my Age column? A link to my sample is below.
- I fixed the permissions on the file, so you should be able to access it now.
- Your formula worked to isolate the 19+ Adults from the list, but it did not parse for those who did log reading vs. those who did not (Column I). If I add a third criterion (AVID!$I$2:$I$500,">0"), I think that would correctly identify my subset. Do you concur?
