Using Sumproduct when some of the values are null

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
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?

See your thread in m.p.excel.

Do not multipost or split threads, especially when there is already people
trying help you. It serves no useful purpose, and it can waste time since
responders do not benefit from the other context.


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

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

This will work if you expect a result of 42.32%:
=SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20



"SanCarlosCyclist" wrote:

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
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?
.

On Mar 23, 1:45*pm, JBoulton
wrote:
This will work if you expect a result of 42.32%:
=SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20



"SanCarlosCyclist" wrote:
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
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?
.- Hide quoted text -

- Show quoted text -

Yesssssssssssssssss, it worked!! Woohoooooo!!!! Thanks so much for
your help. This formula was driving me crazy.

"SanCarlosCyclist" wrote:
On Mar 23, 1:45 pm,
JBoulton wrote:
This will work if you expect a result of 42.32%:
=SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20
[....]
Yesssssssssssssssss, it worked!! Woohoooooo!!!!

Exactly the solution I provided and you read an hour earlier in the other
thread. I'm glad you finally tried it.

FYI, there is no need for the parentheses around the ranges. The best way
to write that is:

=SUMPRODUCT(--(A20=A5:A12),B5:B12,C5:C12)/C20


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

"SanCarlosCyclist" wrote in message
...
On Mar 23, 1:45 pm, JBoulton
wrote:
This will work if you expect a result of 42.32%:
=SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20



"SanCarlosCyclist" wrote:
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
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?
.- Hide quoted text -

- Show quoted text -

Yesssssssssssssssss, it worked!! Woohoooooo!!!! Thanks so much for
your help. This formula was driving me crazy.

Thanks so much Joe User and JBoulton for your help.