Data Consolidation in the same sheet.

[JrExceler]

I have been struggling with this task for a long time, and I still cannot find a solution, maybe it's very simple but I cannot find it
I would like to summarize- my data in the same sheet (or another if it's not possible) when I Have 3 different conditions (country, City and Sales Person) and need to create only one line when the 3 conditions are met per unique combination (like sumifs I did below).

The table might have different length and not always the same country city or sales person, the target is to get the unique values and the sum of the sales per month

Your support or guidance will be highly appreciated.

 

[etaf]

what version of excel do you have

will the columns always be the same
then

say in A13
=unique(A3:C9)

will give the first 3 columns
and then a SUMIFS()

=SUMIFS(D$2:D$9 , $A$2:$A$9, $A13 ,$B$2:$B$9, $B13 ,$C$2:$C$9, $C13 )
should be able to copy across and down

BUT that assumes all the columns match up
can be done in a different sheet

otherwise a SUMproduct()

Jan. Feb - is that just Text or is it a date formatted to just show the month

 

[JrExceler]

Thank you for yor answer etaf, my columns are always the same but my rows will change, Jan Feb... is only the title of each month Text Format. Can this be done with a Macro? to have a button that creates the new excel sheet based on the original data provided by an external system?

 

[etaf]

sorry , I dont provide VBA solutions

FYI - i have made up a sheet and copied into XL2BB just for completeness

Book19
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2				Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	mex	slp	A	1	10	100	200	400	800	1600	3200	6400	12800	25600	51200
4	mex	slp	A	2	20	200	400	800	1600	3200	6400	12800	25600	51200	102400
5	mex	ags	A	3	30	300	600	1200	2400	4800	9600	19200	38400	76800	153600
6	mex	ags	A	4	40	400	800	1600	3200	6400	12800	25600	51200	102400	204800
7	mex	ags	A	5	50	500	1000	2000	4000	8000	16000	32000	64000	128000	256000
8	mex	slp	B	6	60	600	1200	2400	4800	9600	19200	38400	76800	153600	307200
9	mex	slp	B	7	70	700	1400	2800	5600	11200	22400	44800	89600	179200	358400
10
11
12	mex	slp	A	3	30	300	600	1200	2400	4800	9600	19200	38400	76800	153600
13	mex	ags	A	12	120	1200	2400	4800	9600	19200	38400	76800	153600	307200	614400
14	mex	slp	B	13	130	1300	2600	5200	10400	20800	41600	83200	166400	332800	665600
15
Sheet1

Cell Formulas
Range	Formula
F3:F9	F3	=E3*10
G3:O9	G3	=F3*2
A12:C14	A12	=UNIQUE(A3:C9)
D12:O14	D12	=SUMIFS(D$3:D$9,$A$3:$A$9,$A12,$B$3:$B$9,$B12,$C$3:$C$9,$C12)
Dynamic array formulas.

 

[JrExceler]

Thank you for your support etaf, I can work with this solution and I think it can be implemented with BVA just getting the table range and pasting the formula in the new sheet with the given length. I will investigate how to do it I'm not so good at Excel but keep practicing.

 

[etaf]

whats BVA
and as asked what version of Excel????

EDIT - oh, BVA is that VBA - yes it could be done I just do not provide solutions in VBA here

 

[JrExceler]

Sorry VBA,
My Excel version is 365.

 

[etaf]

would be worth adding that to the profile - makes a lot of difference to solutions as 365 has many functions that other versions do not have
hopefully a member with VBA will be along to answer

 

[alansidman]

Here is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column2", "Column3"}, {{"Jan", each List.Sum([#"1/1/2024"]), type number}, {"Feb", each List.Sum([#"2/1/2024"]), type number}, {"Mar", each List.Sum([#"3/1/2024"]), type number}, {"Apr", each List.Sum([#"4/1/2024"]), type number}, {"May", each List.Sum([#"5/1/2024"]), type number}, {"Jun", each List.Sum([#"6/1/2024"]), type number}, {"Jul", each List.Sum([#"7/1/2024"]), type number}, {"Aug", each List.Sum([#"8/1/2024"]), type number}, {"Sep", each List.Sum([#"9/1/2024"]), type number}, {"Oct", each List.Sum([#"10/1/2024"]), type number}, {"Nov", each List.Sum([#"11/1/2024"]), type number}, {"Dec", each List.Sum([#"12/1/2024"]), type number}})
in
    #"Grouped Rows"