Counting Text

[kwedde01]

I am trying to write a formula which will count the value "Yes" in columns I:I to M:M. However, the value "Yes" should not be counted more than once on the same row.

Ex.

Column I:I Column J:J
Yes Yes
No Yes
No No

Total unique "Yes" = 2

Can someone help please?

 

[MarkAndrews]

=countif(i:i, "Yes")

 

[Andrew Poulsom]

Try eg:

=SUMPRODUCT(--(((I1:I1000="Yes")+(J1:J1000="Yes"))>0))

 

[kwedde01]

It works well. However, in some of the cells the value "#NUM!" is found, and this gives an error when I run the formula. How do I get around this

 

[Domenic]

Try...

=SUMPRODUCT(--(ISNUMBER(1/(I1:I1000="Yes"))+ISNUMBER(1/(J1:J1000="Yes"))>0))

Since you have Columns I through M, maybe...

=SUMPRODUCT(--(MMULT(ISNUMBER(1/(I1:M1000="Yes"))+0,ROW(I1:INDEX(I1:I1000,COLUMNS(I1:M1000)))^0)>0))

Hope this helps!

 

[kwedde01]

=SUMPRODUCT(--(MMULT(ISNUMBER(1/(Excel1!I:M="Yes"))+0,ROW(Excel1!I2:INDEX(Excel1!I:I,COLUMNS(Excel1!I:M)))^0)>0))

When I use the above formula I now get an error message of #VALUE!

What could be the problem?

 

[Andrew Poulsom]

You can't use whole columns with SUMPRODUCT. Limit the number of rows, like 1:1000 in the examples given.

 

[kwedde01]

Thank you very much

 

[Domenic]

In additin to Andrew's comments, MMULT has a limit. If the output exceeds 5460 cells, #VALUE! will be returned. If this is the case, download and install the free add-in Morefunc.xll and use MMULT.EXT instead.

Otherwise, without the add-in...

=SUMPRODUCT(--(COUNTIF(OFFSET(I2:M1000,ROW(I2:M1000)-ROW(I2),0,1),"Yes")>0))

Hope this helps!