Excel Function to count a given value across two columns, where it counts if the value is in one col-CodePudding

I am trying to develop an excel formula which will return a value which is a count of the number of rows which contain the term ‘Yes’, as shown in the table below, where for A1:B4 it should return 3.

-|A —|B— |  
1|Yes|—— |
2|Yes|Yes|
3|—- |—- |
4|—- |Yes|

CodePudding user response：

=SUMPRODUCT(--($A$1:$B$4="Yes"))-SUMPRODUCT(--($A$1:$A$4&$B$1:$B$4="YesYes"))

You can use SUMPRODUCT to count how many single Yes values you got and then subtract the double Yes values.

Other options:

=COUNTIF($A$1:$B$4;"Yes")-COUNTIFS($A$1:$A$4;"Yes";$B$1:$B$4;"Yes")

=SUMPRODUCT(--($A$1:$A$4&$B$1:$B$4={"Yes-";"-Yes";"YesYes"}))

CodePudding user response：

What about a helper column, based on =IF(OR(A1="Yes",B1="Yes),1,0), this generates 1 if there is a "Yes" and 0 otherwise. All what's left is taking the sum of that column.

CodePudding user response：

Try SUMPRODUCT().

=SUMPRODUCT(--(((A1:A15="Yes") (B1:B15="Yes"))>0))

Or below formula with O365.

=SUM(--(BYROW(A1:B11,LAMBDA(x,OR(x="Yes")))))