How to fetch sales data from specific cells based on moving months

[Wadebro]

Hello!

Trying to optimize my workflow as a boomer excel user in my business job.
Every month I get a report with hundreds of rows from different customers with 3 columns per month in this order (Gross Sales, Net Sales, Sales Quantity).
January numbers start from column H so that would mean the following:

Column H: Gross Sales
Column I: Net Sales
Column J: Sales Quantity

Each month adds another 3 columns so K, L, M for February and so on.

Simple enough. Now at the bottom of the excel data sheet there is a row with the "Overall Result" for all of the Gross Sales etc. data from all the customer rows.
My Question is the following:

How do I formulate a "=" sentence that knows how to look up the current total values of Gross Sales, Net Sales and Sales Quantity respectively and print out the number in a very simple table that has
the previos numbers summed up until the latest FULL month so that would be June as of writing this question. The Formula should for example print the sum of all "Gross Sales" data from Jan-Jun into a single cell for further use.

The idea here is to keep the master excel data updated automatically so that I wouldn't have to do so much manual work each month updating all the 20+ customers I have with each coming with hundreds of rows of data.

I've trie VLOOKUP, LOOKUP, INDEX while toying around with setting the table array and looking at MAX function to retrieve the bottom most row but I just can't get it to work.

Hopefully it was clear what I want to achieve. Thank you for taking the time to look at this and for all the help!

Mehiläinen.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
1	JAN 2022			FEB 2022			MAR 2022			APR 2022
2	Gross Sales	Net Sales	Sales Quantity	Gross Sales	Net Sales	Sales Quantity	Gross Sales	Net Sales	Sales Quantity	Gross Sales	Net Sales	Sales Quantity
3	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE
4
5
6
7	460,00	460,00	2							460,00	460,00	2
8										176,00	176,00	2
9	15,50	15,50	1							31,00	31,00	2
10	174,72	174,72	1
11	212,50	212,50	2	93,75	93,75	1
12
13				112,00	112,00	1
14	862,72	862,72	6	205,75	205,75	2				667,00	667,00	6
15
16	480,00	480,00	6	400,00	400,00	5				800,00	800,00	10
17										230,00	230,00	1
18	62,00	62,00	4
19
20	375,00	375,00	4
21	118,40	118,40	2
22	1 035,40	1 035,40	16	400,00	400,00	5				1 030,00	1 030,00	11
23
24
25
26							352,00	352,00	4
27				62,00	62,00	4
28	937,50	937,50	10
29	118,40	118,40	2				118,40	118,40	2
30
31
32
33
34
35	1 055,90	1 055,90	12	62,00	62,00	4	470,40	470,40	6
2022

 

[Skyybot]

What are your Row labels?

 

[awoohaw]

my suggestion, if you haven't gone too far down the cross tab rabbit hole to rebuild your data table with a column for month. You're mixing data gathering and reporting in your worksheet, which initially may seem great, but leads to downstream issues when you need to change something. Here is your data, and then your data represented as a pivot table:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
24	Item	Month	Gross Sales	Net Sales	Sales Quantity				Column Labels
25	Item1	Jan-22	460	460	2				Jan			Feb			Mar			Apr
26	Item2	Jan-22
27	Item3	Jan-22	15.5	15.5	1
28	Item4	Jan-22	174.72	174.72	1			Row Labels	Gr Sales	Nt Sales	Sales Qty	Gr Sales	Nt Sales	Sales Qty	Gr Sales	Nt Sales	Sales Qty	Gr Sales	Nt Sales	Sales Qty
29	Item5	Jan-22	212.5	212.5	2			Item1	460	460	2	0	0	0	0	0	0	460	460	2
30	Item6	Jan-22						Item10	62	62	4	0	0	0	0	0	0	0	0	0
31	Item7	Jan-22	862.72	862.72	6			Item11	375	375	4	0	0	0	0	0	0	0	0	0
32	Item8	Jan-22	480	480	6			Item12	118.4	118.4	2	0	0	0	0	0	0	0	0	0
33	Item9	Jan-22						Item13	1035.4	1035.4	16	400	400	5	0	0	0	1030	1030	11
34	Item10	Jan-22	62	62	4			Item14	0	0	0	0	0	0	352	352	4	0	0	0
35	Item11	Jan-22	375	375	4			Item15	0	0	0	62	62	4	0	0	0	0	0	0
36	Item12	Jan-22	118.4	118.4	2			Item16	937.5	937.5	10	0	0	0	0	0	0	0	0	0
37	Item13	Jan-22	1035.4	1035.4	16			Item17	118.4	118.4	2	0	0	0	118.4	118.4	2	0	0	0
38	Item14	Jan-22						Item18	1055.9	1055.9	12	62	62	4	470.4	470.4	6	0	0	0
39	Item15	Jan-22						Item2	0	0	0	0	0	0	0	0	0	176	176	2
40	Item16	Jan-22	937.5	937.5	10			Item3	15.5	15.5	1	0	0	0	0	0	0	31	31	2
41	Item17	Jan-22	118.4	118.4	2			Item4	174.72	174.72	1	0	0	0	0	0	0	0	0	0
42	Item18	Jan-22	1055.9	1055.9	12			Item5	212.5	212.5	2	93.75	93.75	1	0	0	0	0	0	0
43	Item1	Feb-22						Item6	0	0	0	112	112	1	0	0	0	0	0	0
44	Item2	Feb-22						Item7	862.72	862.72	6	205.75	205.75	2	0	0	0	667	667	6
45	Item3	Feb-22						Item8	480	480	6	400	400	5	0	0	0	800	800	10
46	Item4	Feb-22						Item9	0	0	0	0	0	0	0	0	0	230	230	1
47	Item5	Feb-22	93.75	93.75	1
48	Item6	Feb-22	112	112	1
49	Item7	Feb-22	205.75	205.75	2
50	Item8	Feb-22	400	400	5
51	Item9	Feb-22
52	Item10	Feb-22
53	Item11	Feb-22
54	Item12	Feb-22
55	Item13	Feb-22	400	400	5
56	Item14	Feb-22
57	Item15	Feb-22	62	62	4
58	Item16	Feb-22
59	Item17	Feb-22
60	Item18	Feb-22	62	62	4
61	Item1	Mar-22
62	Item2	Mar-22
63	Item3	Mar-22
64	Item4	Mar-22
65	Item5	Mar-22
66	Item6	Mar-22
67	Item7	Mar-22
68	Item8	Mar-22
69	Item9	Mar-22
70	Item10	Mar-22
71	Item11	Mar-22
72	Item12	Mar-22
73	Item13	Mar-22
74	Item14	Mar-22	352	352	4
75	Item15	Mar-22
76	Item16	Mar-22
77	Item17	Mar-22	118.4	118.4	2
78	Item18	Mar-22	470.4	470.4	6
79	Item1	Apr-22	460	460	2
80	Item2	Apr-22	176	176	2
81	Item3	Apr-22	31	31	2
82	Item4	Apr-22
83	Item5	Apr-22
84	Item6	Apr-22
85	Item7	Apr-22	667	667	6
86	Item8	Apr-22	800	800	10
87	Item9	Apr-22	230	230	1
88	Item10	Apr-22
89	Item11	Apr-22
90	Item12	Apr-22
91	Item13	Apr-22	1030	1030	11
92	Item14	Apr-22
93	Item15	Apr-22
94	Item16	Apr-22
95	Item17	Apr-22
96	Item18	Apr-22
wadebro

Cell Formulas
Range	Formula
C25:E96	C25	=IF(INDEX($B$4:$M$21,MATCH($A25,$A$4:$A$21,0),MATCH($B25&C$24,$B$1:$M$1&$B$2:$M$2,0))="","", INDEX($B$4:$M$21,MATCH($A25,$A$4:$A$21,0),MATCH($B25&C$24,$B$1:$M$1&$B$2:$M$2,0)))

 

[Wadebro]

The only one that should matter is that at the bottom of the data table in column A is "Overall Result" which calculates each column's rows to the very bottom row. Like this

Mehiläinen.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
645						Result
646	Overall Result							42 856,34	37 376,34	479	32 779,04	32 758,89	395	30 015,94
2022

I am able to get the MAXIMUM value of the best month printed into a cell by using this formula:

=SUM(MAX(CHOOSE(MONTH(TODAY())-1; H:H; K:K; N:N; Q:Q; T:T; W:W; Z:Z; AC:AC; AF:AF; AI:AI; AL:AL; AO:AO)))

But it doesnt know how to calculate all of the months together up until June. Columns H, K, N etc... Are the Gross Sales columns. I was hoping to achieve the result I wanted by calculating the maximum number of each column per month which is of course the bottom row "Overall Result" number together but this is still a bit off.

 

[awoohaw]

So, this is a report and not the master data? Can you share a sample of the master data? Then share what kind of out put you want? (Are there other output objectives you want other than what you have in post #4?).

 

[Wadebro]

Apologies for the confusion. I am not quite sure if I understand the specific difference between a master data and the report.
I get a massive excel file with ALL of the data including all the customers our company handles. I first use the "Analysis" tools to sort out all the customers that belong to me and make a separate excel for further use. From that excel I would need to collect "Gross Sales", "Net Sales" and "Sales Quantity" data for another excel where I collect all the data from all of my different customers.
This data I then turn into graphs and other stuff for presentation.

My goal is to make that manual workflow a lot shorter by being able to just fetch the correct sum of cell values based on the month I am in.
So for example now it's July and I would need to get the Gross Sales data from Jan-Jun. That data resides in columns H:H; K:K; N:N; Q:Q; T:T; W:W; Z:Z.
At the bottom of these columns are the "overall results" so essentially =max(H:H) so I to automate it by calculating essentially as many columns as there are months currently and summing all the overall results together.

 

[awoohaw]

Okay. And you are welcome to ignore if you think this is too much work for you.
My perspective is that you are building a report from the massive data you already have. Then you want to build another report based on that report.

My take is to do everything you do up until you make the cross tab above, and then make individual reports based on that.

Again, it would be much easier if you shared your master data.

 

[Wadebro]

Thank you for your patience as I am not familiar with all the terms. By master data you mean the entirety of the Data Source I have access to? I'm not sure how I can share it as it is company data and wouldn't want it "out there". Or am I completely off here on what I need to share?

 

[awoohaw]

Yes, that is what I mean, and you can sanitize it. column headers are fairly important - but can be altered as needed, the other data can be dummied up. (but you would need to be sure the dummy data makes sense calculation wise).

 

[awoohaw]

Yes, that is what I mean, and you can sanitize it. column headers are fairly important - but can be altered as needed, the other data can be dummied up. (but you would need to be sure the dummy data makes sense calculation wise).

and you only need columns that are necessary to the report(s) you want to create. I am pretty sure xl2bb will not copy hidden columns.