Search code examples
excelcountifarray-formulas

excel countifs with AND & OR

I'm trying to create an Excel function that counts instances that meet 3 criteria. For example data in sample data, I want to:

1)For each type of 'Structure' count number of rows in which 2)'Horizontal' is greater than zero, OR 3)'Vertical' is greater than zero

Expected result for example data in column D.

An array formula of the type

=SUM(IF(($B$2:$B$7>0)+($C$2:$C$7>0),1,0))

counts in accordance with criteria 2) and 3) but does not group by 'Structure'.

Any thoughts?

Much appreciated.

Solution

  • Try this Array formula:

    =SUM(IF(($A$2:$A$7= A2)*(($B$2:$B$7>0)+($C$2:$C$7>0)),1,0))

    Remember to use Ctrl-Shift-Enter.

    The * is used for And and the + for Or. So this will pick up only those that have Structure in column A and has a greater than 0 in one or both of column B or column C