Sumifs?? Left?? Dish sales

I

IvanYerk

Board Regular
Joined
Aug 26, 2018
Messages
61
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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]
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
You mean like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Location[/td][td=bgcolor:#70AD47]SumSales[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555652 Houston[/td][td=bgcolor:#E2EFDA]
53​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]555884 phoenix[/td][td]
116​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555102 chicago[/td][td=bgcolor:#E2EFDA]
24​
[/td][/tr]
[/table]
 
Upvote 0
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.

[TABLE="class: head, width: 0"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Location[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SumSales[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]555652[/TD]
[TD="bgcolor: #E2EFDA"]
26​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]555884[/TD]
[TD]
58​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]555102 [/TD]
[TD="bgcolor: #E2EFDA"]
12​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
this is ok?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Location.1[/td][td=bgcolor:#70AD47]SumSale[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555652[/td][td=bgcolor:#E2EFDA]
53​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]555884[/td][td]
116​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555102[/td][td=bgcolor:#E2EFDA]
24​
[/td][/tr]
[/table]
 
Upvote 0
Except I only need to sum Dish sales, not Bowls. But yes, this is generally what I need
 
Upvote 0
How about


Book1
ABCDEFG
1LocationProduct IDProduct DescSales
2555652 Houston10047108Dish 12"455565226
3555652 Houston10047109Dish 16"1155588458
4555652 Houston10047110Dish 18"1155510212
5555652 Houston10047128Bowl 6"3
6555652 Houston10047129Bowl 8"12
7555652 Houston10047130Bowl 10"12
8555884 phoenix10047108Dish 12"8
9555884 phoenix10047109Dish 16"25
10555884 phoenix10047110Dish 18"25
11555884 phoenix10047128Bowl 6"8
12555884 phoenix10047129Bowl 8"25
13555884 phoenix10047130Bowl 10"25
14555102 chicago10047109Dish 16"6
15555102 chicago10047110Dish 18"6
16555102 chicago10047129Bowl 8"6
17555102 chicago10047130Bowl 10"6
Sheet1
Cell Formulas
RangeFormula
G2=SUMPRODUCT((LEFT($A$2:$A$17,6)*1=F2)*(LEFT($C$2:$C$17,4)="Dish")*($D$2:$D$17))
 
Upvote 0
sure, so with Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Location.1[/td][td=bgcolor:#70AD47]SumDish[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555652[/td][td=bgcolor:#E2EFDA]
26​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]555884[/td][td]
58​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]555102[/td][td=bgcolor:#E2EFDA]
12​
[/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
Hi, here is another option you could try.


Excel 2013/2016
ABCDEFG
1LocationProduct IDProduct DescSales
2555652 Houston10047108Dish 12"455565226
3555652 Houston10047109Dish 16"1155588458
4555652 Houston10047110Dish 18"1155510212
5555652 Houston10047128Bowl 6"3
6555652 Houston10047129Bowl 8"12
7555652 Houston10047130Bowl 10"12
8555884 phoenix10047108Dish 12"8
9555884 phoenix10047109Dish 16"25
10555884 phoenix10047110Dish 18"25
11555884 phoenix10047128Bowl 6"8
12555884 phoenix10047129Bowl 8"25
13555884 phoenix10047130Bowl 10"25
14555102 chicago10047109Dish 16"6
15555102 chicago10047110Dish 18"6
16555102 chicago10047129Bowl 8"6
17555102 chicago10047130Bowl 10"6
Sheet1
Cell Formulas
RangeFormula
G2=SUMIFS(D:D,A:A,F2&"*",C:C,"Dish*")
 
Upvote 0
It never ceases to amaze me how wonderful a place like this can be - thank you so much for your help!! Worked perfectly.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
Conditional Formatting for Matching Odd Rows only
Replies
4
Views
264
gtd526
gtd526
P
  • Question Question
Process and Indentation
Replies
1
Views
214
Fuji
F
S
  • Question Question
Dynamic Array to make 2 Columns
Replies
6
Views
301
Peter_SSs
Peter_SSs
D
  • Question Question
Assign dates as late as possible without duplicates
Replies
1
Views
149
AlphaFrog
AlphaFrog
S
  • Solved
Individual Ranking
2
Replies
11
Views
470
sksanjeev786
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top