Vba to sum values based on Customer ID

E

Elliottj2121

Board Regular
Joined
Apr 15, 2021
Messages
56
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a dataset that has invoice information for customers. I am wondering if there is a way to add a "Total" line and sum the invoice amounts based on the customer number. each customer has a unique customer number. The top data set is an example of what I currently have, and the bottom data set is what I am looking for. Note that the customer number, Customer name, Contact and email also have to be in the total line. Thank you!


Customer #CustomerInv#DateAgeAmountPOContactEmail
100Alpha Company0110997106/07/202418$1854.55Alpha8Joealphaco@anymail.com
100Alpha Company0110997206/07/202418$1423.79Alpha8Joealphaco@anymail.com
100Alpha Company0110997306/07/202418$1187.33Alpha8Joealphaco@anymail.com
100Alpha Company0111025406/07/202418$324.82Alpha8Joealphaco@anymail.com
101Beta Inc.0111030606/07/202418$30.24Beta9MikeBeta@anymail.com
101Beta Inc.0111070606/11/202414$181.44Beta10MikeBeta@anymail.com
102Gamma LLC0110996806/12/202413$10015.19Gamma88PaulGamma@anymail.com
102Gamma LLC0111229006/13/202412$2451.80Gamma88PaulGamma@anymail.com
102Gamma LLC0241606006/03/202422$238.09Gamma101PaulGamma@anymail.com
102Gamma LLC0241664406/04/202421$602.47Gamma101PaulGamma@anymail.com
103Delta LLP0241606306/10/202415$853.17456123JohnDelta@anymail.com
Customer #CustomerInv#DateAgeAmountPOContactEmail
100Alpha Company0110997106/07/202418$1854.55Alpha8Joealphaco@anymail.com
100Alpha Company0110997206/07/202418$1423.79Alpha8Joealphaco@anymail.com
100Alpha Company0110997306/07/202418$1187.33Alpha8Joealphaco@anymail.com
100Alpha Company0111025406/07/202418$324.82Alpha8Joealphaco@anymail.com
100Alpha CompanyTOTAL$4790.49Joealphaco@anymail.com
101Beta Inc.0111030606/07/202418$30.24Beta9MikeBeta@anymail.com
101Beta Inc.0111070606/11/202414$181.44Beta10MikeBeta@anymail.com
101Beta Inc.TOTAL$211.68MikeBeta@anymail.com
102Gamma LLC0110996806/12/202413$10015.19Gamma88PaulGamma@anymail.com
102Gamma LLC0111229006/13/202412$2451.80Gamma88PaulGamma@anymail.com
102Gamma LLC0241606006/03/202422$238.09Gamma101PaulGamma@anymail.com
102Gamma LLC0241664406/04/202421$602.47Gamma101PaulGamma@anymail.com
102Gamma LLCTOTAL$13307.55PaulGamma@anymail.com
103Delta LLP0241606306/10/202415$853.17456123PaulDelta@anymail.com
103Delta LLPTOTAL$853.17Delta@anymail.com
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I don't recommend adding summary rows in your dataset. If you ever want to filter or summarize the values, it'll make it hard to do so. Maybe something like this

Book1
ABCDEFGHIJ
1Customer #CustomerInv#DateAgeAmountPOContactEmail Total
2100Alpha Company11099716/7/2024181854.55Alpha8Joealphaco@anymail.com 
3100Alpha Company11099726/7/2024181423.79Alpha8Joealphaco@anymail.com 
4100Alpha Company11099736/7/2024181187.33Alpha8Joealphaco@anymail.com 
5100Alpha Company11102546/7/202418324.82Alpha8Joealphaco@anymail.com4,790.49
6101Beta Inc.11103066/7/20241830.24Beta9MikeBeta@anymail.com 
7101Beta Inc.11107066/11/202414181.44Beta10MikeBeta@anymail.com211.68
8102Gamma LLC11099686/12/20241310015.19Gamma88PaulGamma@anymail.com 
9102Gamma LLC11122906/13/2024122451.8Gamma88PaulGamma@anymail.com 
10102Gamma LLC24160606/3/202422238.09Gamma101PaulGamma@anymail.com 
11102Gamma LLC24166446/4/202421602.47Gamma101PaulGamma@anymail.com13,307.55
12103Delta LLP24160636/10/202415853.17456123JohnDelta@anymail.com853.17
Sheet1
Cell Formulas
RangeFormula
J2:J12J2=IF(A2<>A3,SUMIFS(F$1:F2,A$1:A2,A2),"")
 
Upvote 0
Solution
Jeffrey Mahoney said:
I don't recommend adding summary rows in your dataset. If you ever want to filter or summarize the values, it'll make it hard to do so. Maybe something like this

Book1
ABCDEFGHIJ
1Customer #CustomerInv#DateAgeAmountPOContactEmail Total
2100Alpha Company11099716/7/2024181854.55Alpha8Joealphaco@anymail.com 
3100Alpha Company11099726/7/2024181423.79Alpha8Joealphaco@anymail.com 
4100Alpha Company11099736/7/2024181187.33Alpha8Joealphaco@anymail.com 
5100Alpha Company11102546/7/202418324.82Alpha8Joealphaco@anymail.com4,790.49
6101Beta Inc.11103066/7/20241830.24Beta9MikeBeta@anymail.com 
7101Beta Inc.11107066/11/202414181.44Beta10MikeBeta@anymail.com211.68
8102Gamma LLC11099686/12/20241310015.19Gamma88PaulGamma@anymail.com 
9102Gamma LLC11122906/13/2024122451.8Gamma88PaulGamma@anymail.com 
10102Gamma LLC24160606/3/202422238.09Gamma101PaulGamma@anymail.com 
11102Gamma LLC24166446/4/202421602.47Gamma101PaulGamma@anymail.com13,307.55
12103Delta LLP24160636/10/202415853.17456123JohnDelta@anymail.com853.17
Sheet1
Cell Formulas
RangeFormula
J2:J12J2=IF(A2<>A3,SUMIFS(F$1:F2,A$1:A2,A2),"")
Click to expand...
I will never need to summarize or filter values. I have another macro that takes each customer's values and makes a table in an outlook email to send to the customer. I just need a total of each customers invoices to be on that email somewhere. So this will work fine.
 
Upvote 0
You can also use Pivot table.
Book1
ABCDEFGHIJKL
1Customer #CustomerInv#DateAgeAmountPOContactEmail Row LabelsSum of Amount
2100Alpha Company110997145450181854.55Alpha8Joealphaco@anymail.com1004790.49
3100Alpha Company110997245450181423.79Alpha8Joealphaco@anymail.com101211.68
4100Alpha Company110997345450181187.33Alpha8Joealphaco@anymail.com10213307.55
5100Alpha Company11102544545018324.82Alpha8Joealphaco@anymail.com103853.17
6101Beta Inc.1110306454501830.24Beta9MikeBeta@anymail.comGrand Total19162.89
7101Beta Inc.11107064545414181.44Beta10MikeBeta@anymail.com
8102Gamma LLC1109968454551310015.19Gamma88PaulGamma@anymail.com
9102Gamma LLC111229045456122451.8Gamma88PaulGamma@anymail.com
10102Gamma LLC24160604544622238.09Gamma101PaulGamma@anymail.com
11102Gamma LLC24166444544721602.47Gamma101PaulGamma@anymail.com
12103Delta LLP24160634545315853.17456123JohnDelta@anymail.com
Sheet3
 
Upvote 0
You must log in or register to reply here.

Similar threads

SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
138
SFCChase
SFCChase
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
294
Skyybot
Skyybot
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
289
Cubist
Cubist

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

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