Help using SUMPRODUCT

What is in cells A1:A7

Mangesh

In cells A1:A7 are the product codes I want to add up from the master list

Chris

Domenic,

When I do this, it returns #Value!

This is the formula I have in my cell:

{=SUM(('Raw 080405'!B1:B43=TRANSPOSE(E1:E7))*'Raw 080405'!C1:C43)}

Chris

You could probably try:

=sumproduct(--('Data Sheet'A1:A65000=A1,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A2,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A3,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A4,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A5,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A6,'Data Sheet'B1:B65000))+sumproduct(--('Data Sheet'A1:A65000=A7,'Data Sheet'B1:B65000))


- Mangesh

Thanks Mangesh - but I get a problem that the formula gets too long, and Excel truncates it.

I'm referring to an external sheet which has quite a long address, which does not help. I have to use this file, as it's the central sales sheet.

I'd normally use a string of vlookups, but can't do this because of the above reason.

Many thanks for your help,

Chris

If you've confirmed the formula with CONTROL+SHIFT+ENTER, and it looks like you have, then it's probably because your range contains one or more 'null strings' ("") or some other text values.

In this case, try the following formula instead...

=SUMPRODUCT(--(ISNUMBER(MATCH('Raw 080405'!B1:B43,E1:E7,0))),--('Raw 080405'!C1:C43))

...confirmed with ENTER only.

Hope this helps!

The formula given by Domenic works for me.

- Mangesh

Domenic,

You're a genius! Many thanks!

Chris