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?
0
SanCarlosCyclist
3/23/2010 7:52:17 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1591 Views

Similar Articles

[PageSpeed] 4

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" <sancarloscyclist@gmail.com> wrote in message 
news:353c670d-a55f-4e5a-8c82-a824f2d73179@z3g2000yqz.googlegroups.com...
>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? 

0
Joe
3/23/2010 8:06:22 PM
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?
> .
> 
0
Utf
3/23/2010 8:45:02 PM
On Mar 23, 1:45=A0pm, JBoulton <JBoul...@discussions.microsoft.com>
wrote:
> This will work if you expect a result of 42.32%:
> =3DSUMPRODUCT(--(A20=3DA5: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?
>
> > =A0 =A0 =A0 =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 B =A0 =A0=
 =A0 =A0 C
> > Row =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Trend =A0 =A0 Claim=
s
> > 5 =A0 =A0Emergency =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0$=
0
> > 6 =A0 =A0Emergency =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0$=
0
> > 7 =A0 =A0Emergency =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0$=
0
> > 8 =A0 =A0Emergency =A0 =A0 =A0 81.68% =A0 =A0 =A0$24,444
> > 9 =A0 =A0Emergency =A0 =A0 =A0 35.00% =A0 =A0 =A0$164,758
> > 10 =A0 Emergency =A0 =A0 =A0 35.00% =A0 =A0 =A0$215,237
> > 11 =A0 Emergency =A0 =A0 =A0 22.73% =A0 =A0 =A0$105,059
> > 12 =A0 Emergency =A0 =A0 =A0 8.46% =A0 =A0 =A0 $53,760
>
> > 20 =A0 =A0Emergency =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 $56=
3,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=3DA5: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.
0
SanCarlosCyclist
3/23/2010 9:22:25 PM
"SanCarlosCyclist" <sancarloscyclist@gmail.com> wrote:
> On Mar 23, 1:45 pm,
> JBoulton <JBoul...@discussions.microsoft.com> 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" <sancarloscyclist@gmail.com> wrote in message 
news:b61c5f45-d59a-442e-914a-ac0869df6cbd@i25g2000yqm.googlegroups.com...
On Mar 23, 1:45 pm, JBoulton <JBoul...@discussions.microsoft.com>
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. 

0
Joe
3/23/2010 11:17:50 PM
Thanks so much Joe User and JBoulton for your help.
0
SanCarlosCyclist
3/25/2010 3:25:37 AM
Reply:

Similar Artilces: