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

 

[Wadebro]

Ok so how do I share it? Couldn't just paste it here when tried. I imagine you would want to see the whole data. I cleaned all the non-relevant data.

 

[Wadebro]

Master Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1							Cal. year / month	JAN 2023			FEB 2023			MAR 2023			APR 2023			MAY 2023			JUN 2023			JUL 2023			Overall Result
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	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	Customer number		Street Name	Postal Code	Location	Material		EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE	EUR	EUR	PCE
4																				18,84	18,84	2							18,84	18,84	2
5																				133,75	133,75	1	133,75	133,75	1				267,50	267,50	2
6																				152,59	152,59	3	133,75	133,75	1				286,34	286,34	4
7																							21,45	21,45	1				21,45	21,45	1
8																							21,45	21,45	1				21,45	21,45	1
9																				18,84	18,84	2							18,84	18,84	2
10																				8,12	8,12	2							8,12	8,12	2
11											133,75	133,75	1							133,75	133,75	1							267,50	267,50	2
12											133,75	133,75	1							160,71	160,71	5							294,46	294,46	6
13																				47,10	47,10	5							47,10	47,10	5
Ark1

This is how the top part looks when print. Left I just cleared all the data that is on the colored backroung.
The important thing is the bottom part which has the "Overall Result" for each column like so

Master Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
4444																				0,00	0,00	1							0,00	0,00	1
4445																				0,00	0,00	1							0,00	0,00	1
4446	Overall Result							746 289,29	552 456,21	21 389	509 844,47	353 804,50	7 751	596 366,10	443 477,42	7 842	551 764,43	380 733,10	6 793	843 787,33	579 992,09	8 452	608 132,60	455 214,22	23 522	13 601,63	-8 017,92	1 231	3 869 785,85	2 757 659,62	76 980
Ark1

 

[awoohaw]

no, just post a representative same. Some with your customers and some not. And you'd need to show how to distinguish.
Or you can filter the data for your customers only and give a representative sample.

I just saw your two items above. The master (or if already filtered) data should not have any blank columns or rows. It is just what many people call a "flat" file. and no summarization.

 

[awoohaw]

is the first xl2bb in post #8 your "master data". if so, you are getting a report and not data.

 

[Wadebro]

Yes everything is from the same excel so I would guess it's a report then and not the data.

 

[Wadebro]

And yes, the report I get has empty columns by default