LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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.

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula Not Working with Range with Formulas [email protected] Excel Discussion (Misc queries) 4 February 1st 06 02:01 PM
Array Formula to Pick Average SludgeQuake Excel Discussion (Misc queries) 3 January 11th 06 12:59 AM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM
Array Formula Matthew S Excel Discussion (Misc queries) 4 October 28th 05 05:49 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 07:35 AM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"