count of number of rows within a range with a certain text
in [Functions]
|
From: cooey on 13 May 2010 12:36 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!
From: T. Valko on 13 May 2010 12:52 >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" <cocoologist(a)gmail.com> wrote in message news:f3b4deb9-9423-4c2f-a359-f8a882c26173(a)s4g2000prh.googlegroups.com... > 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!
From: Luke M on 13 May 2010 13:25 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" <cocoologist(a)gmail.com> wrote in message news:f3b4deb9-9423-4c2f-a359-f8a882c26173(a)s4g2000prh.googlegroups.com... > 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!
From: Eduardo on 13 May 2010 13:32 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! > . >
From: T. Valko on 13 May 2010 14:32
>=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" <lukemoraga(a)nospam.com> wrote in message news:uvPwaFs8KHA.1436(a)TK2MSFTNGP06.phx.gbl... > 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" <cocoologist(a)gmail.com> wrote in message > news:f3b4deb9-9423-4c2f-a359-f8a882c26173(a)s4g2000prh.googlegroups.com... >> 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! > > |