Counting Text

kwedde01

Board Regular
Joined
Jun 9, 2005
Messages
140
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?
 

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"

kwedde01

Board Regular
Joined
Jun 9, 2005
Messages
140
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
Joined
Mar 10, 2004
Messages
19,696

ADVERTISEMENT

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
Joined
Jun 9, 2005
Messages
140
=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
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top