Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 457
Default 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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default 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!
.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If Count is not in a certain range, delete Rows CarolynRatcliffe Excel Discussion (Misc queries) 0 February 23rd 10 05:52 AM
How do i count the number of text Entries of one word over a range Chris Eaton Excel Worksheet Functions 2 January 18th 10 11:32 AM
count number of rows with 2 matching text cells smcmoran Excel Worksheet Functions 5 September 29th 08 05:53 PM
Count number of rows, where non relevant rows are hidden Pieter Excel Discussion (Misc queries) 2 November 8th 06 01:24 PM
Count rows and insert number to count them. Mex Excel Discussion (Misc queries) 6 August 23rd 06 02:29 AM


All times are GMT +1. The time now is 12:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"