Forum Discussion
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' and Column B is less than 499, count total value of cells between C2:F1000
I have attached an example table below.
I have got as far as
=COUNTIFS(A2:A1000,"Douglas",B2:B1000,">499",C2:C1000,"*")
However, I need the total value of range C2:F1000. If I input this range the formula fails.
Any assistance is appreciated! Thank you
3 Replies
A solution using PIVOTBY:
=LET( cc_col, IF(CC > 499, ">499", "<499"), k, BYROW(data, COUNTA), PIVOTBY(names, cc_col, k, SUM) )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.
Try:
=sum( (A2:A1000="Douglas")*(B2:B1000>499)*( (C2:C1000<>"")+(D2:D1000<>"")+(E2:E1000<>"")+(F2:F1000<>"") >0) )
