Using Sumproduct when some of the values are null

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

 

[Joe User]

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

 

[JBoulton]

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

 

[SanCarlosCyclist]

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








- Show quoted text -

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

 

[Joe User]

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

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








- Show quoted text -

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

 

[SanCarlosCyclist]

Thanks so much Joe User and JBoulton for your help.