# Counting Text

#### kwedde01

##### Board Regular
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### MarkAndrews

##### Well-known Member
=countif(i:i, "Yes")

#### Andrew Poulsom

##### MrExcel MVP
Try eg:

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

#### kwedde01

##### Board Regular
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

##### MrExcel MVP

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

##### Board Regular
=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

##### MrExcel MVP

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

#### kwedde01

##### Board Regular
Thank you very much

#### Domenic

##### MrExcel MVP
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.

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

Hope this helps!

Replies
27
Views
413
Replies
2
Views
107
Replies
14
Views
267
Replies
12
Views
258
Replies
12
Views
127

1,136,261
Messages
5,674,704
Members
419,520
Latest member
Jennifer4Dillon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back