Summing totals on different sheet if criteria is met

JakariKryze

New Member
Joined
Dec 14, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I have data in one sheet that i am manipulating and i need to sum all the totals of items of the same type together on a summary page. i am still new to VBA and so i dont have a code to start out with.
This is the Blackboard Sheet and the one below it is the Summary sheet. I need to sum all the totals of the same type and have it appear in the summary page in the second column. thanks for your help.
Retail Tran DateProduct Department NameSum of Product Net Sales AmtSum of Product Tax AmountSales Total
12/8/2021 0:00​
ACF - Bulk - Tax
$14.25​
$1.14​
$15.39​
12/3/2021 0:00​
ACF - Drinks - Tax
$18.00​
$1.27​
$19.27​
12/8/2021 0:00​
ACF - Drinks - Tax
$13.50​
$1.04​
$14.54​
12/7/2021 0:00​
ACF - Drinks - Tax
$8.10​
$0.57​
$8.67​
12/2/2021 0:00​
ACF - Drinks - Tax
$11.70​
$0.90​
$12.60​
12/10/2021 0:00​
ACF - Drinks - Tax
$44.70​
$3.27​
$47.97​
12/6/2021 0:00​
ACF - Drinks - Tax
$31.50​
$2.36​
$33.86​
12/9/2021 0:00​
ACF - Drinks - Tax
$60.30​
$4.27​
$64.57​
12/2/2021 0:00​
ACF - Gift Boxes - No Tax
$38.85​
$0.00​
$38.85​
12/1/2021 0:00​
ACF - Gift Boxes - No Tax
$50.00​
$0.00​
$50.00​
12/6/2021 0:00​
ACF - Gift Boxes - Tax
$59.95​
$4.20​
$64.15​
12/8/2021 0:00​
ACF - Gift Boxes - Tax
$39.90​
$0.00​
$39.90​
12/3/2021 0:00​
ACF - Gift Boxes - Tax
$19.95​
$1.40​
$21.35​
12/10/2021 0:00​
ACF - Gift Boxes - Tax
$99.95​
$7.00​
$106.95​
12/9/2021 0:00​
ACF - Gift Boxes - Tax
$29.95​
$2.10​
$32.05​
12/8/2021 0:00​
ACF - Misc - No Tax
$461.54​
$0.00​
$461.54​
12/1/2021 0:00​
ACF - Misc - No Tax
$248.65​
$0.00​
$248.65​
12/2/2021 0:00​
ACF - Misc - No Tax
$150.00​
$0.00​
$150.00​
12/9/2021 0:00​
ACF - Misc - No Tax
$825.72​
$0.00​
$825.72​
12/7/2021 0:00​
ACF - Misc - No Tax
$2,597.60​
$0.00​
$2,597.60​
12/10/2021 0:00​
ACF - Misc - No Tax
$192.27​
$0.00​
$192.27​
12/3/2021 0:00​
ACF - Misc - No Tax
$127.25​
$0.00​
$127.25​
12/2/2021 0:00​
ACF - Misc - Tax
$2,886.36​
$202.06​
$3,088.42​
12/10/2021 0:00​
ACF - Misc - Tax
$171.19​
$11.96​
$183.15​
12/7/2021 0:00​
ACF - Misc - Tax
$42.16​
$2.38​
$44.54​
12/9/2021 0:00​
ACF - Misc - Tax
$255.02​
$17.91​
$272.93​
12/3/2021 0:00​
ACF - Misc - Tax
$226.78​
$16.36​
$243.14​
12/6/2021 0:00​
ACF - Misc - Tax
$137.80​
$10.21​
$148.01​
12/8/2021 0:00​
ACF - Misc - Tax
$149.83​
$10.91​
$160.74​
12/1/2021 0:00​
ACF - Misc - Tax
$272.35​
$19.84​
$292.19​
12/9/2021 0:00​
ACF - Mixes
$59.85​
$4.19​
$64.04​
12/1/2021 0:00​
ACF - Mixes
$28.80​
$1.40​
$30.20​
12/6/2021 0:00​
ACF - Mixes
$104.58​
$7.31​
$111.89​
12/8/2021 0:00​
ACF - Mixes
$19.95​
$1.39​
$21.34​
12/7/2021 0:00​
ACF - Mixes
$5.90​
$0.41​
$6.31​
12/10/2021 0:00​
ACF - Mixes
$73.70​
$4.75​
$78.45​
12/3/2021 0:00​
ACF - Mixes
$69.16​
$4.84​
$74.00​
12/9/2021 0:00​
ACF - Mixes - NT
$78.95​
$0.00​
$78.95​
12/1/2021 0:00​
ACF - Swag - Tax
$23.85​
$0.00​
$23.85​
12/7/2021 0:00​
ACF - Swag - Tax
$7.99​
$0.56​
$8.55​
12/6/2021 0:00​
ACF - Swag - Tax
$6.36​
$0.51​
$6.87​
12/3/2021 0:00​
ACF - Swag - Tax
$7.95​
$0.64​
$8.59​
12/2/2021 0:00​
ACF - Swag - Tax
$9.99​
$0.70​
$10.69​
12/10/2021 0:00​
ACF - Thistle & Rose - NT
$17.56​
$0.00​
$17.56​
12/10/2021 0:00​
ACF - Thistle & Rose - Ta
$516.48​
$33.74​
$550.22​
12/2/2021 0:00​
ACF - Thistle & Rose - Ta
$56.60​
$2.96​
$59.56​
12/6/2021 0:00​
ACF - Thistle & Rose - Ta
$168.51​
$6.70​
$175.21​
12/7/2021 0:00​
ACF - Thistle & Rose - Ta
$42.11​
$1.20​
$43.31​
12/9/2021 0:00​
ACF - Thistle & Rose - Ta
$162.65​
$10.34​
$172.99​
12/3/2021 0:00​
ACF - Thistle & Rose - Ta
$143.00​
$8.22​
$151.22​
12/1/2021 0:00​
ACF - Thistle & Rose - Ta
$29.75​
$0.88​
$30.63​
12/8/2021 0:00​
ACF - Thistle & Rose - Ta
$200.80​
$8.78​
$209.58​
ACF - BULK - TAX
ACF - BULK
ACF - DRINKS - TAX
ACF - DRINKS
ACF - SWAG - TAX
ACF - SWAG
ACF - THISTLE & ROSE - TAX
ACF - THISTLE & ROSE
ACF - BAKED GOODS - TAX
ACF - BAKED GOODS
ACF - GIFT BOXES - TAX
ACF - GIFT BOXES
ACF - MISC - TAX
ACF - MISC
ACF - SHIPPING
E STORE SALES - TAX
E STORE SALES
FREIGHT & POSTAGE
ACF - MIXES - TAX
ACF - MIXES
ACF - CHOC COVERED ASSORT
GIFT CARDS SOLD
OVER/SHORT
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Any particular for a macro, as this can done with a simple formula
+Fluff 1.xlsm
ABCDEFGHIJ
1Retail Tran DateProduct Department NameSum of Product Net Sales AmtSum of Product Tax AmountSales TotalProduct Department NameSum of Product Net Sales AmtSum of Product Tax AmountSales Total
208/12/2021 00:00ACF - Bulk - Tax£14.25£1.14£15.39ACF - Bulk - Tax14.251.1415.39
303/12/2021 00:00ACF - Drinks - Tax£18.00£1.27£19.27ACF - Drinks - Tax187.813.68201.48
408/12/2021 00:00ACF - Drinks - Tax£13.50£1.04£14.54ACF - Gift Boxes - No Tax88.85088.85
507/12/2021 00:00ACF - Drinks - Tax£8.10£0.57£8.67ACF - Gift Boxes - Tax249.714.7264.4
602/12/2021 00:00ACF - Drinks - Tax£11.70£0.90£12.60ACF - Misc - No Tax4603.0304603.03
710/12/2021 00:00ACF - Drinks - Tax£44.70£3.27£47.97ACF - Misc - Tax4141.49291.634433.12
806/12/2021 00:00ACF - Drinks - Tax£31.50£2.36£33.86ACF - Mixes361.9424.29386.23
909/12/2021 00:00ACF - Drinks - Tax£60.30£4.27£64.57ACF - Mixes - NT78.95078.95
1002/12/2021 00:00ACF - Gift Boxes - No Tax£38.85£0.00£38.85ACF - Swag - Tax56.142.4158.55
1101/12/2021 00:00ACF - Gift Boxes - No Tax£50.00£0.00£50.00ACF - Thistle & Rose - NT17.56017.56
1206/12/2021 00:00ACF - Gift Boxes - Tax£59.95£4.20£64.15ACF - Thistle & Rose - Ta1319.972.821183.14
1308/12/2021 00:00ACF - Gift Boxes - Tax£39.90£0.00£39.90
1403/12/2021 00:00ACF - Gift Boxes - Tax£19.95£1.40£21.35
1510/12/2021 00:00ACF - Gift Boxes - Tax£99.95£7.00£106.95
1609/12/2021 00:00ACF - Gift Boxes - Tax£29.95£2.10£32.05
1708/12/2021 00:00ACF - Misc - No Tax£461.54£0.00£461.54
1801/12/2021 00:00ACF - Misc - No Tax£248.65£0.00£248.65
1902/12/2021 00:00ACF - Misc - No Tax£150.00£0.00£150.00
2009/12/2021 00:00ACF - Misc - No Tax£825.72£0.00£825.72
2107/12/2021 00:00ACF - Misc - No Tax£2,597.60£0.00£2,597.60
2210/12/2021 00:00ACF - Misc - No Tax£192.27£0.00£192.27
2303/12/2021 00:00ACF - Misc - No Tax£127.25£0.00£127.25
2402/12/2021 00:00ACF - Misc - Tax£2,886.36£202.06£3,088.42
2510/12/2021 00:00ACF - Misc - Tax£171.19£11.96£183.15
2607/12/2021 00:00ACF - Misc - Tax£42.16£2.38£44.54
2709/12/2021 00:00ACF - Misc - Tax£255.02£17.91£272.93
2803/12/2021 00:00ACF - Misc - Tax£226.78£16.36£243.14
2906/12/2021 00:00ACF - Misc - Tax£137.80£10.21£148.01
3008/12/2021 00:00ACF - Misc - Tax£149.83£10.91£160.74
3101/12/2021 00:00ACF - Misc - Tax£272.35£19.84£292.19
3209/12/2021 00:00ACF - Mixes£59.85£4.19£64.04
3301/12/2021 00:00ACF - Mixes£28.80£1.40£30.20
3406/12/2021 00:00ACF - Mixes£104.58£7.31£111.89
3508/12/2021 00:00ACF - Mixes£19.95£1.39£21.34
3607/12/2021 00:00ACF - Mixes£5.90£0.41£6.31
3710/12/2021 00:00ACF - Mixes£73.70£4.75£78.45
3803/12/2021 00:00ACF - Mixes£69.16£4.84£74.00
3909/12/2021 00:00ACF - Mixes - NT£78.95£0.00£78.95
4001/12/2021 00:00ACF - Swag - Tax£23.85£0.00£23.85
4107/12/2021 00:00ACF - Swag - Tax£7.99£0.56£8.55
4206/12/2021 00:00ACF - Swag - Tax£6.36£0.51£6.87
4303/12/2021 00:00ACF - Swag - Tax£7.95£0.64£8.59
4402/12/2021 00:00ACF - Swag - Tax£9.99£0.70£10.69
4510/12/2021 00:00ACF - Thistle & Rose - NT£17.56£0.00£17.56
4610/12/2021 00:00ACF - Thistle & Rose - Ta£516.48£33.74£550.22
4702/12/2021 00:00ACF - Thistle & Rose - Ta£56.60£2.96£59.56
4806/12/2021 00:00ACF - Thistle & Rose - Ta£168.51£6.70£175.21
4907/12/2021 00:00ACF - Thistle & Rose - Ta£42.11£1.20£43.31
5009/12/2021 00:00ACF - Thistle & Rose - Ta£162.65£10.34£172.99
5103/12/2021 00:00ACF - Thistle & Rose - Ta£143.00£8.22£151.22
5201/12/2021 00:00ACF - Thistle & Rose - Ta£29.75£0.88£30.63
5308/12/2021 00:00ACF - Thistle & Rose - Ta£200.80£8.78$209.58
List
Cell Formulas
RangeFormula
G2:G12G2=UNIQUE(B2:B53)
H2:J12H2=SUMIFS(C:C,$B:$B,$G2#)
Dynamic array formulas.
 
Upvote 0
Any particular for a macro, as this can done with a simple formula
+Fluff 1.xlsm
ABCDEFGHIJ
1Retail Tran DateProduct Department NameSum of Product Net Sales AmtSum of Product Tax AmountSales TotalProduct Department NameSum of Product Net Sales AmtSum of Product Tax AmountSales Total
208/12/2021 00:00ACF - Bulk - Tax£14.25£1.14£15.39ACF - Bulk - Tax14.251.1415.39
303/12/2021 00:00ACF - Drinks - Tax£18.00£1.27£19.27ACF - Drinks - Tax187.813.68201.48
408/12/2021 00:00ACF - Drinks - Tax£13.50£1.04£14.54ACF - Gift Boxes - No Tax88.85088.85
507/12/2021 00:00ACF - Drinks - Tax£8.10£0.57£8.67ACF - Gift Boxes - Tax249.714.7264.4
602/12/2021 00:00ACF - Drinks - Tax£11.70£0.90£12.60ACF - Misc - No Tax4603.0304603.03
710/12/2021 00:00ACF - Drinks - Tax£44.70£3.27£47.97ACF - Misc - Tax4141.49291.634433.12
806/12/2021 00:00ACF - Drinks - Tax£31.50£2.36£33.86ACF - Mixes361.9424.29386.23
909/12/2021 00:00ACF - Drinks - Tax£60.30£4.27£64.57ACF - Mixes - NT78.95078.95
1002/12/2021 00:00ACF - Gift Boxes - No Tax£38.85£0.00£38.85ACF - Swag - Tax56.142.4158.55
1101/12/2021 00:00ACF - Gift Boxes - No Tax£50.00£0.00£50.00ACF - Thistle & Rose - NT17.56017.56
1206/12/2021 00:00ACF - Gift Boxes - Tax£59.95£4.20£64.15ACF - Thistle & Rose - Ta1319.972.821183.14
1308/12/2021 00:00ACF - Gift Boxes - Tax£39.90£0.00£39.90
1403/12/2021 00:00ACF - Gift Boxes - Tax£19.95£1.40£21.35
1510/12/2021 00:00ACF - Gift Boxes - Tax£99.95£7.00£106.95
1609/12/2021 00:00ACF - Gift Boxes - Tax£29.95£2.10£32.05
1708/12/2021 00:00ACF - Misc - No Tax£461.54£0.00£461.54
1801/12/2021 00:00ACF - Misc - No Tax£248.65£0.00£248.65
1902/12/2021 00:00ACF - Misc - No Tax£150.00£0.00£150.00
2009/12/2021 00:00ACF - Misc - No Tax£825.72£0.00£825.72
2107/12/2021 00:00ACF - Misc - No Tax£2,597.60£0.00£2,597.60
2210/12/2021 00:00ACF - Misc - No Tax£192.27£0.00£192.27
2303/12/2021 00:00ACF - Misc - No Tax£127.25£0.00£127.25
2402/12/2021 00:00ACF - Misc - Tax£2,886.36£202.06£3,088.42
2510/12/2021 00:00ACF - Misc - Tax£171.19£11.96£183.15
2607/12/2021 00:00ACF - Misc - Tax£42.16£2.38£44.54
2709/12/2021 00:00ACF - Misc - Tax£255.02£17.91£272.93
2803/12/2021 00:00ACF - Misc - Tax£226.78£16.36£243.14
2906/12/2021 00:00ACF - Misc - Tax£137.80£10.21£148.01
3008/12/2021 00:00ACF - Misc - Tax£149.83£10.91£160.74
3101/12/2021 00:00ACF - Misc - Tax£272.35£19.84£292.19
3209/12/2021 00:00ACF - Mixes£59.85£4.19£64.04
3301/12/2021 00:00ACF - Mixes£28.80£1.40£30.20
3406/12/2021 00:00ACF - Mixes£104.58£7.31£111.89
3508/12/2021 00:00ACF - Mixes£19.95£1.39£21.34
3607/12/2021 00:00ACF - Mixes£5.90£0.41£6.31
3710/12/2021 00:00ACF - Mixes£73.70£4.75£78.45
3803/12/2021 00:00ACF - Mixes£69.16£4.84£74.00
3909/12/2021 00:00ACF - Mixes - NT£78.95£0.00£78.95
4001/12/2021 00:00ACF - Swag - Tax£23.85£0.00£23.85
4107/12/2021 00:00ACF - Swag - Tax£7.99£0.56£8.55
4206/12/2021 00:00ACF - Swag - Tax£6.36£0.51£6.87
4303/12/2021 00:00ACF - Swag - Tax£7.95£0.64£8.59
4402/12/2021 00:00ACF - Swag - Tax£9.99£0.70£10.69
4510/12/2021 00:00ACF - Thistle & Rose - NT£17.56£0.00£17.56
4610/12/2021 00:00ACF - Thistle & Rose - Ta£516.48£33.74£550.22
4702/12/2021 00:00ACF - Thistle & Rose - Ta£56.60£2.96£59.56
4806/12/2021 00:00ACF - Thistle & Rose - Ta£168.51£6.70£175.21
4907/12/2021 00:00ACF - Thistle & Rose - Ta£42.11£1.20£43.31
5009/12/2021 00:00ACF - Thistle & Rose - Ta£162.65£10.34£172.99
5103/12/2021 00:00ACF - Thistle & Rose - Ta£143.00£8.22£151.22
5201/12/2021 00:00ACF - Thistle & Rose - Ta£29.75£0.88£30.63
5308/12/2021 00:00ACF - Thistle & Rose - Ta£200.80£8.78$209.58
List
Cell Formulas
RangeFormula
G2:G12G2=UNIQUE(B2:B53)
H2:J12H2=SUMIFS(C:C,$B:$B,$G2#)
Dynamic array formulas.
but how to you get it to go between 2 different sheets? i need the summary to go on a different sheet because i will continue to move the items to others sheets and manipulate the blackboard sheet.
 
Upvote 0
Just add the sheet name of the data sheet to the formula.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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