COUNTIFS with OR

[anteagles20]

Hi,

I am trying to do a countifs formula which checks if criteria_range1=criteria OR criteria_range2=criteria2

Here is a simplified example:

1 2
2 3
1 1
2 2
3 1
1 1
3 1
1 3

The formula would check if the number 1 appears in either column a or column b, so in this instance the formula would return a count of 6.

Thanks

[daddylonglegs]

Try this syntax

=SUMPRODUCT(((A2:A10=1)+(B2:B10=1)>0)+0)

[NBVC]

Try:

=SUMPRODUCT(--((A2:A9=1)+(B2:B9=1)>0))

where A2:B9 is the range.

[anteagles20]

Thanks guys!