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

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!

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

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?

--
Biff
Microsoft Excel MVP


"cooey" wrote in message
...
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!

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

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).

--
Best Regards,

Luke M
"cooey" wrote in message
...
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!

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

hi,
I assume the NO are in column R

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

"cooey" wrote:

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!
.

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

=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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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).

--
Best Regards,

Luke M
"cooey" wrote in message
...
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!

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

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