Search code examples
excelexcel-formulacountifarray-formulas

Excel: Countif of Countif

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!

Solution

  • 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.