Sumproduct with blanks and the numeric 0

[taurus99]

In this spreadsheet, I am trying to figure out ...

how many rows labeled zero are less than 43%.
How many rows labeled one are less than 43%.
How many rows labeled two are less than 43% etc ....

the formula works for all numbers except zero. It wants to count blank rows
as zero and adding them to my total. How do I get this formula to recognize
the zero for a zero and blank for a blank?

=SUMPRODUCT((E16:E4500<43%)*(F16:F4500=0))

(In my real spreadsheet rows 3417 through 4500 are blank)

Sample:

A B C D E F G
1 45% 0
2 68% 1
3 22% 0
4 19% 2
5 14% 2
6 47% 0
7 86% 1
8 -2% 0
9 62% 1
10
11
12

 

[T. Valko]

=SUMPRODUCT((E16:E4500<43%)*(F16:F4500=0))

A couple of things you can do...

Add a test for cell is not blank:

In my real spreadsheet rows 3417 through 4500 are blank

Or, use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

 

[taurus99]

awesome, thank you once again!!!!!!!

A couple of things you can do...

Add a test for cell is not blank:



Or, use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

awesome, thank you once again!!!!!!!