Order out of chaos: how to get 1 sum total from multiple rows of a repeating entity?

Siamrooster

New Member
Joined
Oct 5, 2017
Messages
5
Hi Mr. Excel forum,

Very grateful for any suggestions on how to achieve objective for this spreadsheet. Searched the forum for a while but don't even know how to categorize the question.


  • The spreadsheet in question has 107,821 rows of data.
  • Each row holds 1 years product volume for a given store.
  • The challenge is that the data is organized by sales team, not by store.
  • Therefore each store appears multiple times.
  • The stores are identified by a unique code (this is a column).
  • And the sales teams are identified by a unique code (this is a column).
  • See data model below

The objective is to be able to 'count' each store only 1 time, and to get a total 'sum' for each store.
We need to know how many stores we are serving, and the volume for each one.

Of course the entire process can be done manually - sorting by store number, creating a new row - Sum of volume for the store - but this will take forever.

Thank you very much in advance!

Best regards

SR

StoreID Sales team code Total sales

273 X1 $2,000
273 X2 $3,000
273 X3 $1,500
273 X4 $600
645 X1 $4,000
645 X2 $300
645 X3 $250
645 X4 $1,600
921 X1 $900
921 X2 $2,400
921 X3 $1,300
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to the Forum!

This is a bit of a guess. If it's not what you're looking for, perhaps you could post the results you are expecting to see?

E2: {=INDEX(A$1:A$11,MATCH(0,COUNTIF(E$1:E1,A$1:A$11),0))} array-entered, ie with CTRL-SHIFT-ENTER keys
F2: =SUMIF(A$1:A$11,E2,C$1:C$11)


Excel 2010
ABCDEF
1273X1$2,000StoreTotal
2273X2$3,000273$7,100
3273X3$1,500645$6,150
4273X4$600921$4,600
5645X1$4,000
6645X2$300$17,850
7645X3$250
8645X4$1,600
9921X1$900
10921X2$2,400
11921X3$1,300
12
13$17,850
1
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

Excel has a built-in Pivot Table feature (on the Insert ribbon tab) to do exactly that sort of summary.


Book1
ABCDEFG
1StoreIDSales team codeTotal salesSum of Total sales
2273X1$2,000StoreIDTotal
3273X2$3,0002737,100
4273X3$1,5006456,150
5273X4$6009214,600
6645X1$4,000Grand Total17,850
7645X2$300
8645X3$250
9645X4$1,600
10921X1$900
11921X2$2,400
12921X3$1,300
PT
 
Upvote 0
Hi Stephen,

Thank you very much for the response! I think you have nailed it. Need to sit with your formula and work through it. This will take me awhile.

Row E will give me unique sales column, and Row F gives the total sales per store.

One follow up based on an input perhaps I should have included...there are many other rows of data.

In fact, the first few columns of data are identifiers (like region, store category) that we need to retain in connection with your Row E, so that we can sort by region and get a single row for each store with the sales data.

Does that make sense? Is there a more efficient way for me to demonstrate the data structure, and make very easy the output required (if above not clear)?

Thank you again. My first time to ever post on a forum for expert assistance. Your time is really appreciated.

Best regards,

SR
 
Upvote 0
Upvote 0
Posting screenshots would be most helpful, e.g. as Peter and I did in our responses above.

Have a look here at Part B here for a couple of ways you can do this: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Hello again,

My thanks again in advance for your taking a look at this once more. If too difficult by forum and better to refer over to Mr. Excel Consulting that would be fine. Let's take one more stab.

I made a dummy data set that has nearly identical parameters/variables - rows/columns, pasted here. And put in the basic analyses to pose for this data set. Let me know what you think.

Best regards, SR

[TABLE="width: 1060"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Sales
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Region
[/TD]
[TD]Type
[/TD]
[TD]Town
[/TD]
[TD]Store ID
[/TD]
[TD]Store Name
[/TD]
[TD]Store Type
[/TD]
[TD]Address
[/TD]
[TD]Sales Team ID
[/TD]
[TD]Salesman Name
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]Total 2016 sales
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]East Central
[/TD]
[TD]Private
[/TD]
[TD]Brisbane
[/TD]
[TD]123
[/TD]
[TD]Malcolm's
[/TD]
[TD]Jokes and Magic
[/TD]
[TD]34 High Street
[/TD]
[TD]x22
[/TD]
[TD]Neville
[/TD]
[TD]100
[/TD]
[TD]110
[/TD]
[TD]95
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]East Central
[/TD]
[TD]Private
[/TD]
[TD]Brisbane
[/TD]
[TD]123
[/TD]
[TD]Malcolm's
[/TD]
[TD]Jokes and Magic
[/TD]
[TD]34 High Street
[/TD]
[TD]x23
[/TD]
[TD]Howard
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD]75
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]East Central
[/TD]
[TD]Private
[/TD]
[TD]Brisbane
[/TD]
[TD]123
[/TD]
[TD]Malcolm's
[/TD]
[TD]Jokes and Magic
[/TD]
[TD]34 High Street
[/TD]
[TD]x24
[/TD]
[TD]Keating
[/TD]
[TD]300
[/TD]
[TD]50
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]1,500
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Southwest
[/TD]
[TD]Partnership
[/TD]
[TD]Perth
[/TD]
[TD]789
[/TD]
[TD]Abbott's
[/TD]
[TD]Whigs
[/TD]
[TD]15 Tony Ave
[/TD]
[TD]x22
[/TD]
[TD]Neville
[/TD]
[TD]100
[/TD]
[TD]110
[/TD]
[TD]95
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Southwest
[/TD]
[TD]Partnership
[/TD]
[TD]Perth
[/TD]
[TD]789
[/TD]
[TD]Abbott's
[/TD]
[TD]Whigs
[/TD]
[TD]16 Tony Ave
[/TD]
[TD]x23
[/TD]
[TD]Howard
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD]75
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Southwest
[/TD]
[TD]Partnership
[/TD]
[TD]Perth
[/TD]
[TD]789
[/TD]
[TD]Abbott's
[/TD]
[TD]Whigs
[/TD]
[TD]17 Tony Ave
[/TD]
[TD]x24
[/TD]
[TD]Keating
[/TD]
[TD]300
[/TD]
[TD]50
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]1,500
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Southwest
[/TD]
[TD]Partnership
[/TD]
[TD]Perth
[/TD]
[TD]789
[/TD]
[TD]Abbott's
[/TD]
[TD]Whigs
[/TD]
[TD]18 Tony Ave
[/TD]
[TD]x27
[/TD]
[TD]John
[/TD]
[TD]200
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x22
[/TD]
[TD]Neville
[/TD]
[TD]100
[/TD]
[TD]110
[/TD]
[TD]95
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x23
[/TD]
[TD]Howard
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD]75
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x24
[/TD]
[TD]Keating
[/TD]
[TD]300
[/TD]
[TD]50
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]1,500
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x27
[/TD]
[TD]John
[/TD]
[TD]200
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x29
[/TD]
[TD]Kevin
[/TD]
[TD]60
[/TD]
[TD]40
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]900
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Southeast
[/TD]
[TD]Co-op
[/TD]
[TD]Melbourne
[/TD]
[TD]888
[/TD]
[TD]Ruddy's
[/TD]
[TD]Coffee and Perks
[/TD]
[TD]26/1 High Street
[/TD]
[TD]x30
[/TD]
[TD]Michael
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x22
[/TD]
[TD]Neville
[/TD]
[TD]100
[/TD]
[TD]110
[/TD]
[TD]95
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x23
[/TD]
[TD]Howard
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD]75
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x24
[/TD]
[TD]Keating
[/TD]
[TD]300
[/TD]
[TD]50
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]1,500
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x27
[/TD]
[TD]John
[/TD]
[TD]200
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1,000
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x29
[/TD]
[TD]Kevin
[/TD]
[TD]60
[/TD]
[TD]40
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]900
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x30
[/TD]
[TD]Michael
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1,200
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]Dead Central
[/TD]
[TD]Bizarre
[/TD]
[TD]Alice Springs
[/TD]
[TD]15
[/TD]
[TD]Gillard's
[/TD]
[TD]Smokes and Mirrors
[/TD]
[TD]27 High Street
[/TD]
[TD]x35
[/TD]
[TD]Eileen
[/TD]
[TD]200
[/TD]
[TD]500
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD]2,000
[/TD]
[/TR]
</tbody>[/TABLE]


The analyses are very simple and straight forward:

Analyses

1 Total number of stores: Cell count would count 20 stores. There are only 4

2 Total sales per store: Getting this from a 100k row dbase would require summing for each outlet, creating a new row disconnected to other parameters that sums all sales teams sales for that store in order to get total sales per outlet. Must be a faster way

3 Sales per region: would want to be able to Total sales (2) and sort by region so that total sales could be known for Dead Central

4 Sales by other paramters: As with 3, this would allow getting total sales by Region, Type, or Town - essentially geography and category
 
Upvote 0
I still think a Pivot Table might be of use to you. I have just copied that sample data repeatedly for 120,000 rows and created a PT with 'Region' in the Rows area (drag & drop to get it there) and 'Total 2016 sales' in the Values area.
The following result in columns O:P was created in the blink of an eye.


Book1
ABCDEFGHIJKLMNOPQ
1RegionTypeTownStore IDStore NameStore TypeAddressSales Team IDSalesman NameJanFebMarTotal 2016 salesSum of Total 2016 sales
2East CentralPrivateBrisbane123Malcolm'sJokes and Magic34 High Streetx22Neville100110951,200RegionTotal
3East CentralPrivateBrisbane123Malcolm'sJokes and Magic34 High Streetx23Howard50100751,000Dead Central52,835,200
4East CentralPrivateBrisbane123Malcolm'sJokes and Magic34 High Streetx24Keating300502001,500East Central22,214,800
5SouthwestPartnershipPerth789Abbott'sWhigs15 Tony Avex22Neville100110951,200Southeast40,827,200
6SouthwestPartnershipPerth789Abbott'sWhigs16 Tony Avex23Howard50100751,000Southwest28,218,800
7SouthwestPartnershipPerth789Abbott'sWhigs17 Tony Avex24Keating300502001,500Grand Total144,096,000
8SouthwestPartnershipPerth789Abbott'sWhigs18 Tony Avex27John200501001,000
9SoutheastCo-opMelbourne888Ruddy'sCoffee and Perks26/1 High Streetx22Neville100110951,200
Sheet1


If I now want to see totals for each salesman ..
1. Click anywhere in the PT itself and the PT fields dialog should reappear (if it had gone)
2. Remove Region from the rows area and replace with Salesman name.

Virtually instantly this is produced


Book1
NOPQ
1Sum of Total 2016 sales
2Salesman NameTotal
3Eileen12,008,000
4Howard24,016,000
5John18,012,000
6Keating36,024,000
7Kevin10,807,200
8Michael14,409,600
9Neville28,819,200
10Grand Total144,096,000
11
Sheet1


Similarly, you could exchange Total 2016 sales for Feb in the values area of the PT if you wanted the results for a single month


Book1
NOPQ
1Sum of Feb
2Salesman NameTotal
3Eileen3,002,000
4Howard2,401,600
5John900,600
6Keating1,200,800
7Kevin480,320
8Michael1,200,800
9Neville2,641,760
10Grand Total11,827,880
11
Sheet1
 
Upvote 0
Thanks a lot Peter! Clear from your demonstrated solution the key to this is to use pivot tables. No experience with these therefore will need to develop this skill.

Let me know if I can return the favor in any way.

Great experience on Mr. Excel. Thanks again.

Best regards,

SR
 
Upvote 0
Thanks a lot Peter! Clear from your demonstrated solution the key to this is to use pivot tables. No experience with these therefore will need to develop this skill.

Let me know if I can return the favor in any way.

Great experience on Mr. Excel. Thanks again.

Best regards,

SR
You're welcome. I hope it suits your needs.

BTW, no need for a PM as well, what you have said here is sufficient. :)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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