Sumproduct: Incomplete List

**Wolfwinter** · 03-15-2015, 12:00 AM

Hello, everyone!

In my workplace we have data like this:

Sheet "Paste"

		Bought	Sold
Drinks	Store1	52	64
Drinks	Store2	23	12
Candy	Store1	12	24
Food	Store1	89	98
Food	Store2	65	45

Notice how if there was no products bought or sold in one category, it is excluded completely (at the moment this cannot be changed because of our outdated system).
As we have over 100 categories and I want to compare them on a montly basis I need a new page where even this 0-movement-categories are shown.

Sheet "List"

		Bought	Sold
Drinks	Store1	52	64
Drinks	Store2	23	12
Candy	Store1	12	24
Candy	Store2	0	0
Food	Store1	89	98
Food	Store2	65	45

Before I start showing my formulas I need to stress that I am more familiar with Calc than Excel, which is probably why I cannot get it to work.

I tried using

=(IF(Paste!$A:$A=List!$A2,1,0)*IF(Paste!$B:$B="Store1",1,0)*Paste!C:C)

and it works until it arrives at a non-existant category where it starts to either find the category or store code to be false. (I alternate the second if clause to be equal to "Store1" or "Store2")

So instead I tried a proper sumproduct function instead:

=SUMPRODUCT((Paste!A:A=List!A2)*(Paste!B:B="Store1")*(Paste!C:C))

but it gives me a #VALUE error, I guess because C:C is not a proper value?

By moving a parenthesis I got good result... until it hit a non-existant category again. It does not skip and return 0, instead it returns the next value in the "Paste"-list.

=SUMPRODUCT((Paste!A:A=List!A2)*(Paste!B:B="Store1"))*(Paste!C:C)

I tried using a lookup-formula to find the proper value, but I guess I am too unfamiliar with it to get it to work.

Any help would be very much appreciated.

- Wolfwinter

**FDibbins** · 03-15-2015, 12:11 AM

Hi, welcome to the forum

Notice how if there was no products bought or sold in one category, it is excluded completely

umm no, actually i did not notice (maybe because its not there?

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Wolfwinter** · 03-15-2015, 01:07 AM

Thank you for you quick reply!

I made a simple book with my problem.
I marked the cells where I get the wrong values with red.

Thanks in advance,

- Wolfwinter