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.
Sample Reader Participation Report.xlsx
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. Sample Reader Participation Report.xlsx Read More
