Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Count is not in a certain range, delete Rows | Excel Discussion (Misc queries) | |||
How do i count the number of text Entries of one word over a range | Excel Worksheet Functions | |||
count number of rows with 2 matching text cells | Excel Worksheet Functions | |||
Count number of rows, where non relevant rows are hidden | Excel Discussion (Misc queries) | |||
Count rows and insert number to count them. | Excel Discussion (Misc queries) |