Problem with Sumproduct when some cells are null

S

SanCarlosCyclist

I am having a problem using Sumproduct when some of the cells have
null values. I will try to describe the problem. Please let me know if
you need additional information. Do any of you have any ideas of how I
can fix that sumproduct to get it to work?

A B C
Trend Claims
Emergency $0
Emergency $0
Emergency $0
Emergency 81.68% $24,444
Emergency 35.00% $164,758
Emergency 35.00% $215,237
Emergency 22.73% $105,059
Emergency 8.46% $53,760

Goal is to calculate an aggregate trend for the Emergency Category
excluding those rows that are null trend

I tried SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20
Emergency Result is #Value $563,258
 
S

SanCarlosCyclist

Shouldn't it be

=SUMPRODUCT((A5:AA12=A20)*C5:C12*B5:B12)/C20

--

HTH

Bob









- Show quoted text -
Click to expand...

Hi Bob, the problem is that the null rows change depending on the
category. I want to be able to copy a formula down that I can use that
calculates trend for each category. (There are 75 categories each with
some null rows)
 
B

Bob Phillips

Don't understand what that all means I am afraid.

--

HTH

Bob

Shouldn't it be

=SUMPRODUCT((A5:AA12=A20)*C5:C12*B5:B12)/C20

--

HTH

Bob









- Show quoted text -
Click to expand...

Hi Bob, the problem is that the null rows change depending on the
category. I want to be able to copy a formula down that I can use that
calculates trend for each category. (There are 75 categories each with
some null rows)
 
J

Joe User

SanCarlosCyclist said:
I am having a problem using Sumproduct when some of the
cells have null values. [....]
I tried SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20
Click to expand...

At a minimum:

SUMPRODUCT(--(A20=A5:A12),A5:A12,b5:b12)/c20

In case C20 might be zero or null:

=IF(N(C20)=0, 0,
SUMPRODUCT(--(A20=A5:A12),A5:A12,b5:b12)/c20)


----- original message -----
 
S

SanCarlosCyclist

Below is a little cleaner:

Note cells B5,B6, and B7 are null.

A B C
Row Trend Claims
5 Emergency $0
6 Emergency $0
7 Emergency $0
8 Emergency 81.68% $24,444
9 Emergency 35.00% $164,758
10 Emergency 35.00% $215,237
11 Emergency 22.73% $105,059
12 Emergency 8.46% $53,760

20 Emergency $563,258

My goal is to calculate an aggregate trend for the Emergency Category
excluding those rows that are null trend in cell b20. I tried the
following formula:
SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20
The result s "#Value"
Do you know how to get this to work?
 
J

Joe User

PS....
In case C20 might be zero or null:
Click to expand...

I did not mean to suggest that that might be the cause of your #VALUE error.
It is not. I was just adding the feature to bullet-proof the formula.

The cause of the #VALUE is because your original formula tries to use "null
values" (presumably null strings, not empty cells) in the arithmetic
expression.

By separating the terms into SUMPRODUCT arguments, SUMPRODUCT treats any
non-numeric argument as zero.


----- original message -----

Joe User said:
SanCarlosCyclist said:
I am having a problem using Sumproduct when some of the
cells have null values. [....]
I tried SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20
Click to expand...

At a minimum:

SUMPRODUCT(--(A20=A5:A12),A5:A12,b5:b12)/c20

In case C20 might be zero or null:

=IF(N(C20)=0, 0,
SUMPRODUCT(--(A20=A5:A12),A5:A12,b5:b12)/c20)


----- original message -----

SanCarlosCyclist said:
I am having a problem using Sumproduct when some of the cells have
null values. I will try to describe the problem. Please let me know if
you need additional information. Do any of you have any ideas of how I
can fix that sumproduct to get it to work?

A B C
Trend Claims
Emergency $0
Emergency $0
Emergency $0
Emergency 81.68% $24,444
Emergency 35.00% $164,758
Emergency 35.00% $215,237
Emergency 22.73% $105,059
Emergency 8.46% $53,760

Goal is to calculate an aggregate trend for the Emergency Category
excluding those rows that are null trend

I tried SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20
Emergency Result is #Value $563,258
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Sumproduct when some of the values are null 5
Problem with a Sumproduct formula 3

Top