count of number of rows within a range with a certain text

[cooey]

Hi.

Sample data:
R1: No, Yes, No
R2: Yes, Yes, Yes
R3: No, No, No

How do get a count of the rows that contain No?

So for the sample above, the output i'm looking for is 2 (2 rows
where No exists).

TIA!

 

[T. Valko]

Sample data:

R1: No, Yes, No
R2: Yes, Yes, Yes
R3: No, No, No

Are the Yes/No in separate cells or is No, Yes, No all in one cell?

 

[Luke M]

Possible solution:

=SUM(--(MMULT(--(A1:C3="No"),--(ROW(A1:A3)>0))>0))

Multiples the rectangular true/false array by a single column of 1's (the
ROW function generates this).

 

[Eduardo]

hi,
I assume the NO are in column R

=sumproduct(--($R$1:$R$1000="NO"))

 

[T. Valko]

=SUM(--(MMULT(--(A1:C3="No"),--(ROW(A1:A3)>0))>0))

That's kind of misleading. Specifically, the use of --(ROW(A1:A3)>0). That
would imply that array2 is based on the number of rows in the range.

Array2 needs to be a vertical array based on the number of *columns* in the
range. The posted sample data just happens to have 3 columns and 3 rows of
data so as written that formula will work.

However, if the range was A1:C4 (still 3 columns) and you
used --(ROW(A1:A4)>0), then the formula would fail.

 

[Bernd P]

Hello,

Define name D for your input area/

Then array-enter:
=SUM(--(MMULT(--(D="No"),ROW(INDIRECT("1:"&COLUMNS(D)))/
ROW(INDIRECT("1:"&COLUMNS(D))))>0))

If you install my Excel add-in Sulprobil.xll (see
http://sulprobil.com/html/excel_addin.html) then you can array-enter:
=SUM(--(MMULT(--(D="No"),sbVector(COLUMNS(D),FALSE,1,0))>0))

Regards,
Bernd