Sumproduct with blanks and the numeric 0
 

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

Sumproduct with blanks and the numeric 0
 

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

A couple of things you can do...

Add a test for cell is not blank:

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

In my real spreadsheet rows 3417 through 4500 are blank

Or, use dynamic ranges:

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


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
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

Sumproduct with blanks and the numeric 0
 

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

"T. Valko" wrote:

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

A couple of things you can do...

Add a test for cell is not blank:

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

In my real spreadsheet rows 3417 through 4500 are blank

Or, use dynamic ranges:

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


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
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

Sumproduct with blanks and the numeric 0
 

You're welcome!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
awesome, thank you once again!!!!!!!

"T. Valko" wrote:

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

A couple of things you can do...

Add a test for cell is not blank:

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

In my real spreadsheet rows 3417 through 4500 are blank

Or, use dynamic ranges:

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


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
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