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