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?

	A	B	C	D
1	Location	Product ID	Product Desc	Sales
2	555652 Houston	10047108	Dish 12"	4
3	555652 Houston	10047109	Dish 16"	11
4	555652 Houston	10047110	Dish 18"	11
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

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[sandy666]

You mean like this?

Location	SumSales
555652 Houston	53​
555884 phoenix	116​
555102 chicago	24​

 

[IvanYerk]

More like this - just need dish products only and I can only search on Location ID even though the location id and name are in the same cell.

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Location[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SumSales[/COLOR]
555652	26​
555884	58​
555102	12​

<tbody>
</tbody>

 

[sandy666]

this is ok?

Location.1	SumSale
555652	53​
555884	116​
555102	24​

 

[IvanYerk]

Except I only need to sum Dish sales, not Bowls. But yes, this is generally what I need

 

[Fluff]

How about

Book1
	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	=SUMPRODUCT((LEFT($A$2:$A$17,6)*1=F2)*(LEFT($C$2:$C$17,4)="Dish")*($D$2:$D$17))

 

[sandy666]

sure, so with Power Query

Location.1	SumDish
555652	26​
555884	58​
555102	12​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Location", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Location.1", "Location.2"}),
    Type = Table.TransformColumnTypes(Split,{{"Product Desc", type text}}),
    IF = Table.AddColumn(Type, "Custom", each if Text.Contains([Product Desc], "Dish") then 1 else null),
    Filter = Table.SelectRows(IF, each ([Custom] = 1)),
    Group = Table.Group(Filter, {"Location.1"}, {{"SumDish", each List.Sum([Sales]), type number}})
in
    Group[/SIZE]

 

[FormR]

Hi, here is another option you could try.

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	=SUMIFS(D:D,A:A,F2&"*",C:C,"Dish*")

 

[IvanYerk]

It never ceases to amaze me how wonderful a place like this can be - thank you so much for your help!! Worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback