View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
uw805
 
Posts: n/a
Default 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.