SUMPRODUCT #Value

[ashlad]

I have the following data
Code Type Apr May June.....
012 X £1 £2 £1
012 y £5 £2 £1
014 X £2 £8 £4
YGD Y £2 £2 £6

The Apr, May (month columns) have the following formula in the currency cell
=IF(ISBLANK('Lesley Plan'!$F8),"", SUM(I$5*$G9))

I am using the following sumproduct function to extract a value for each month depending on the code and type eg (total value for April if Code is 012 and Type X). The sumproduct fornula is

=SUMPRODUCT(($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27)*I$9:I$161)

c9:c161 = code
z25 = select code to search for
d9:c161 = Type
y27 = type to search for
i9:161 = April values

My problem is I get the #value error from this formula.

I have change the value reference to a column that has values which are derived from a simple sum equation and it works (eg. =SUM(I29:T29)).

Any help would be very much appreciated.

Cheers

AshLad

 

[maxflia10]

Try replacing the * for a , in the last argument if you want a sum I9:I161

 

[Dave Patton]

The formula looks like it should work.

Is your data in Column I?

You could use this format

=SUMPRODUCT(($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27)*(I$9:I$161))

Ensure that your criteria is in the stated cells.

You can test the sub parts of the formula by highlighting a section and then pressing F9.
Look at results and then press ESC.

HTH Dave

 

[Aladin Akyurek]

On 2002-09-23 11:00, Dave Patton wrote:

The formula looks like it should work.

Is your data in Column I?

You could use this format

=SUMPRODUCT(($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27)*(I$9:I$161))

Ensure that your criteria is in the stated cells.

You can test the sub parts of the formula by highlighting a section and then pressing F9.
Look at results and then press ESC.

HTH Dave

Dave,

My guess is that the range to sum, I$9:I$161, contains formula generated ""...

SUMPRODUCT in

=SUMPRODUCT(($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27)*I$9:I$161)

will attempt to multiply the values in I$9:I$161 with the numeric array that results from

($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27)

before summing... like {1}*{""} which will end up in #VALUE!

The comma in

=SUMPRODUCT(($C$9:$C$161=$Z$25)*($D$9:$D$161=$Y27),I$9:I$161)

"prevents" such a multiplication, apparently a built-in evaluation semantics in SUMPRODUCT. Just for this reason, we ought really to have:

=SUMPRODUCT(($C$9:$C$161=$Z$25)+0,($D$9:$D$161=$Y27)+0,I$9:I$161)

Aladin

 

[ashlad]

Guys,

Sorry for the late reply, posted last thing yesterday.
Thanks very much, the , worked. I don't know why. Aladin explained it but i couldn't grasp the underlying assumptions that he was making.

Cheers

Ashlad