# How to COUNTIFS with mixed criteria

Copper Contributor

# 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

2 Replies

# Re: How to COUNTIFS with mixed criteria

@Mensamom We can't download or copy your file but you may try-

``=COUNTIFS(Avid!\$A\$2:\$A\$500,">=19")+COUNTIFS(Avid!\$A\$2:\$A\$500,"21+")``

# Re: How to COUNTIFS with mixed criteria

1. I fixed the permissions on the file, so you should be able to access it now.
2. 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?