Forum Discussion

Douglas_'s avatar
Douglas_
Copper Contributor
Aug 11, 2025

COUNTIFS for multiple ranges

Hi    I'm struggling to complete this formula. I need to count to the total quantity of cells that have a value but only if they hit all 3 requirements.  E.g. IF Column A says 'Douglas' an...
excel
Formulas and Functions
SnowMan55's avatar
SnowMan55
Bronze Contributor
Aug 11, 2025

A simple technique is 

=   COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $C$2:$C$1000, "*")
  + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $D$2:$D$1000, "*")
  + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $E$2:$E$1000, "*")
  + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $F$2:$F$1000, "*")

(Yes, it works without the extra spaces and even if you don't break it into multiple lines.)  See the attached workbook for more information.

Resources