Help using SUMPRODUCT

I have a list of values on a sheet, with Product code in column 1 and sales quantity in sales 2.

On a separate summary sheet, I need to add up products in a variety of combinations.

I was thinking of using SUMPRODUCT, but I can't get it to work. I want something like this

=sumproduct(--('Data Sheet'A1:A65000=or(A1:A7),'Data Sheet'B1:B65000))

Does anybody have any bright ideas?

Many thanks,

Chris

Try...

=SUM(('Data Sheet'!A1:A65000=TRANSPOSE(A1:A7))*'Data Sheet'!B1:B65000)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

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!