Excel: Countif of Countif

1
votes

I'd like to know if there's a way to calculate two countifs with different data and criteria in a single formula (not row by row or with pivot tables). Let the following example apply:

I have the following table: fields A, B and C for each item (ID). Table header starts in cell T1.

Data Table

I'd like to know how many items have 2 or more fields (A, B, C) with a number greater than 5.

I'd create another column X and use the following formula row by row: (Example for row 2)

=COUNTIF(U2:W2;">5")

and for that new column X (with all the COUNTIF formulas) I would use another COUNTIF

=COUNTIF(X:X;">1)

Is there a way to concatenate both? (I guess with an array formula)

Thanks in advance!

1
excelexcel-formulacountifarray-formulas

1 Answers

6
votes

Try

=SUMPRODUCT((((U2:U11>5)+(V2:V11>5)+(W2:W11>5))>1)*1)

or

=SUMPRODUCT(--(((U2:U11>5)+(V2:V11>5)+(W2:W11>5))>1))

enter image description here

You can also use SUM(IF()) as an array formula like below

=SUM(IF(((U2:U11>5)+(V2:V11>5)+(W2:W11>5))>1, 1, 0))

Above being an array formula needs to be committed by pressing Ctrl+Shift+Enter.