Sumifs?? Left?? Dish sales

[IvanYerk]

I'd like to sum all of the dish sales based on the Location number and Product IDs. Can I do this without creating a helper column?

[TABLE="width: 495"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Product ID[/TD]
[TD]Product Desc[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]555652 Houston[/TD]
[TD]10047108[/TD]
[TD]Dish 12"[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]555652 Houston[/TD]
[TD]10047109[/TD]
[TD]Dish 16"[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]555652 Houston[/TD]
[TD]10047110[/TD]
[TD]Dish 18"[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]555652 Houston[/TD]
[TD]10047128[/TD]
[TD]Bowl 6"[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]555652 Houston[/TD]
[TD]10047129[/TD]
[TD]Bowl 8"[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]555652 Houston[/TD]
[TD]10047130[/TD]
[TD]Bowl 10"[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]555884 phoenix[/TD]
[TD]10047108[/TD]
[TD]Dish 12"[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]555884 phoenix[/TD]
[TD]10047109[/TD]
[TD]Dish 16"[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]555884 phoenix[/TD]
[TD]10047110[/TD]
[TD]Dish 18"[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]555884 phoenix[/TD]
[TD]10047128[/TD]
[TD]Bowl 6"[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]555884 phoenix[/TD]
[TD]10047129[/TD]
[TD]Bowl 8"[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]555884 phoenix[/TD]
[TD]10047130[/TD]
[TD]Bowl 10"[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]555102 chicago[/TD]
[TD]10047109[/TD]
[TD]Dish 16"[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]555102 chicago[/TD]
[TD]10047110[/TD]
[TD]Dish 18"[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]555102 chicago[/TD]
[TD]10047129[/TD]
[TD]Bowl 8"[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]555102 chicago[/TD]
[TD]10047130[/TD]
[TD]Bowl 10"[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

@FormR
Keep forgetting that SUMIFS takes wildcards makes it so much simpler.

 

[IvanYerk]

How would you modify the formula if I wanted to use the 3 product ids for dishes (10047108,10047109, and 10047110) instead of the alpha description field?

 

[FormR]

How would you modify the formula if I wanted to use the 3 product ids for dishes (10047108,10047109, and 10047110) instead of the alpha description field?

Hi, here is one option..

Excel 2013/2016
	A	B	C	D	E	F	G
1	Location	Product ID	Product Desc	Sales
2	555652 Houston	10047108	Dish 12"	4		555652	26
3	555652 Houston	10047109	Dish 16"	11		555884	58
4	555652 Houston	10047110	Dish 18"	11		555102	12
5	555652 Houston	10047128	Bowl 6"	3
6	555652 Houston	10047129	Bowl 8"	12
7	555652 Houston	10047130	Bowl 10"	12
8	555884 phoenix	10047108	Dish 12"	8
9	555884 phoenix	10047109	Dish 16"	25
10	555884 phoenix	10047110	Dish 18"	25
11	555884 phoenix	10047128	Bowl 6"	8
12	555884 phoenix	10047129	Bowl 8"	25
13	555884 phoenix	10047130	Bowl 10"	25
14	555102 chicago	10047109	Dish 16"	6
15	555102 chicago	10047110	Dish 18"	6
16	555102 chicago	10047129	Bowl 8"	6
17	555102 chicago	10047130	Bowl 10"	6
Sheet1

Cell Formulas
Range	Formula
G2	=SUM(SUMIFS(D:D,A:A,F2&"*",B:B,{10047108,10047109,10047110}))

 

[IvanYerk]

Awesome! Thanks again for the help!! This site is magical - I know - a little hyperbolic, but the willingness of people to help is absolutely fantastic.