SUMPRODUCT or SUMIF

[imback2nite]

Hello and thank you for any answers. I have a problem with SUMPRODUCT. I'm using Excel 2003, ancient I know but it's what I have. I'm trying to use =SUMPRODUCT(--(C15:C742="(AW)"),(D15:K742)). The C15:C742 is where the "(AW)" will reside but I get an #VALUE error. Then I use what I thought would work,=SUMPRODUCT(--(C15:J742="(AW)"),(D15:K742)). This will give me a value but from only "D15:D742" column instead of the "D15:K742. The criteria is in the "C" column and the area to be summed is in "D:J" columns. Is the because I'm using Excel 2003? I can use =SUMIF($C:$C,"(AW)",$D:$D)+SUMIF($C:$C,"(AW)",$E:$E)+SUMIF($C:$C,"(AW)",$F:$F)+SUMIF($C:$C,"(AW)",$G:$G)+SUMIF($C:$C,"(AW)",$H:$H)+SUMIF($C:$C,"(AW)",$I:$I)+SUMIF($C:$C,"(AW)",J:J) But I'd like the SUMPRODUCT instead unless there is a reason not to. Any help would be appreciated.

 

[bebo021999]

There are 2 kind of using SUMPRODUCT
=SUMPRODUCT(range1,range2)
(one-one relationship)

or
=SUMPRODUCT(range1*range2)
(one-many relationship)

if range1 size <> range2 size, only 2nd one works.

Try:

=SUMPRODUCT((C15:J742="(AW)")*D15:K742)

 

[Peter_SSs]

Try

=SUMPRODUCT((C15:C742="(AW)")*(D15:K742))

 

[imback2nite]

Yes, I've tried both of those and I get a #VALUE error.

 

[bebo021999]

The might be cells with text string in D15:K742?

 

[bebo021999]

=SUMPRODUCT((C15:J742="(AW)")*IF(ISERROR(D15:K742+0),0,D15:K742))

Confirmed with Ctrl-Shift-Enter combination

 

[HongRu]

Try.

=SUMPRODUCT((C15:C742="(AW)")*(D15:K742))

 

[imback2nite]

bebo12999 I thought of that but I do get a value with =SUMPRODUCT(--(C15:J742="(AW)"),(D15:K742)) but only sums the 'D' column. Otherwise I just keep getting #VALUE errors.

 

[bebo021999]

bebo12999 I thought of that but I do get a value with =SUMPRODUCT(--(C15:J742="(AW)"),(D15:K742)) but only sums the 'D' column. Otherwise I just keep getting #VALUE errors.

As I have mentioned in #2, the two ranges are not same size then it does not work.
Have you tried mine in #6?

 

[Peter_SSs]

Put this in a vacant cell and tell us what the result is.

=COUNTA(D15:K742)-COUNT(D15:K742)