Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
Hi!
Your posted "table" is columns A thru F and H yet your description uses column G? Try this array entered: =SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Columns: A B C D E F H Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
Biff,
Thanks for the help. Sorry about the "G" in my post. They were all supposed to be "F"s. The formula you gave me works except for one small thing. It seems to count "x" with the non-zero numbers, and I need to count it as if it were a zero. How can I tweak the formula to do that? Thanks. "Biff" wrote: Hi! Your posted "table" is columns A thru F and H yet your description uses column G? Try this array entered: =SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Columns: A B C D E F H Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
Try this:
=SUMPRODUCT(--(MMULT(--(A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUMN(A1:F5 )^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Biff, Thanks for the help. Sorry about the "G" in my post. They were all supposed to be "F"s. The formula you gave me works except for one small thing. It seems to count "x" with the non-zero numbers, and I need to count it as if it were a zero. How can I tweak the formula to do that? Thanks. "Biff" wrote: Hi! Your posted "table" is columns A thru F and H yet your description uses column G? Try this array entered: =SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Columns: A B C D E F H Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
I figured it out. Thanks for your help.
"uw805" wrote: Biff, Thanks for the help. Sorry about the "G" in my post. They were all supposed to be "F"s. The formula you gave me works except for one small thing. It seems to count "x" with the non-zero numbers, and I need to count it as if it were a zero. How can I tweak the formula to do that? Thanks. "Biff" wrote: Hi! Your posted "table" is columns A thru F and H yet your description uses column G? Try this array entered: =SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Columns: A B C D E F H Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare row contents w/Sumproduct or Array formula?
Actually, you don't need the "--" inside MMULT:
=SUMPRODUCT(--(MMULT((A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUM N(A1:F5)^0))=3),--(H1:H5="A")) Biff "Biff" wrote in message ... Try this: =SUMPRODUCT(--(MMULT(--(A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUMN(A1:F5 )^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Biff, Thanks for the help. Sorry about the "G" in my post. They were all supposed to be "F"s. The formula you gave me works except for one small thing. It seems to count "x" with the non-zero numbers, and I need to count it as if it were a zero. How can I tweak the formula to do that? Thanks. "Biff" wrote: Hi! Your posted "table" is columns A thru F and H yet your description uses column G? Try this array entered: =SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A")) Biff "uw805" wrote in message ... Columns: A B C D E F H Row 1- 0 0 1 2 0 5 A Row 2- 2 0 0 0 0 1 B Row 3- 0 0 1 1 x 1 B Row 4- 4 x 0 0 0 0 B Row 5- 7 0 0 0 2 2 A I am looking for a formula that returns the number of rows in which columns A-F contain at least 3 numbers greater than zero and in which column H="A". In this case, it would return "2" because rows 1 and 5 fit this criteria. ("x" is the only letter that sometimes appears in the cols A-F, and I would like to treat it as a zero.) For a single row, I would use a formula like this: =Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A")) But when I try to convert the A1:G1 to account for multiple rows, it instead counts the entire range A1:G7. I know this can be done by comparing each column individually, but in my actual spreadsheet I am analyzing 30+ columns. Is this possible with sumproduct or an array formula? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Array Formula | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |