Sum and display cells based on unique values of a column

[rombo]

Sir, I am trying to find an excel formula to sum and display cells based on unique values of a column. Below there are two tables first is the data table and second is the result. Not able to find a formula to get the second table from table1. Pls help.

Name	Dept	Job	Zone	Area	Mon	Tue	Wed
George	Spices	GA	South	TN	0	40	40
George	Spices	GB	South	TN	20	40	20
Abby	Milk	A1	West	MUM	0	0	5
Abby	Milk	A2	West	MUM	0	10	0
Mike	Corn	Maddy	#N/A	#N/A	230	70	0
Mike	Cement	Mux	#N/A	#N/A	110	20	0
Mike	Roof	Rick	#N/A	#N/A	0	0	30
Need Formula to get below table from above table
Name	Zone	Area	Mon	Tue	Wed
George	South	TN	20​	80​	60​
Abby	West	MUM		10​	5​
Mike	#N/A	#N/A	340​	90​	30​

 

[DRSteele]

I think this might do it.

MrExcel posts20.xlsx
	D	E	F	G	H	I	J	K
6	Name	Dept	Job	Zone	Area	Mon	Tue	Wed
7	George	Spices	GA	South	TN	0	40	40
8	George	Spices	GB	South	TN	20	40	20
9	Abby	Milk	A1	West	MUM	0	0	5
10	Abby	Milk	A2	West	MUM	0	10	0
11	Mike	Corn	Maddy	#N/A	#N/A	230	70	0
12	Mike	Cement	Mux	#N/A	#N/A	110	20	0
13	Mike	Roof	Rick	#N/A	#N/A	0	0	30
14
15
16	Name	Zone	Area	Mon	Tue	Wed
17	George	South	TN	20	80	60
18	Abby	West	MUM	0	10	5
19	Mike	#N/A	#N/A	340	90	30
Sheet18

Cell Formulas
Range	Formula
G17:I19	G17	=SUMIFS(I$7:I$13,$D$7:$D$13,$D17,$G$7:$G$13,$E17,$H$7:$H$13,$F17)

 

[rombo]

I think this might do it.

MrExcel posts20.xlsx
	D	E	F	G	H	I	J	K
6	Name	Dept	Job	Zone	Area	Mon	Tue	Wed
7	George	Spices	GA	South	TN	0	40	40
8	George	Spices	GB	South	TN	20	40	20
9	Abby	Milk	A1	West	MUM	0	0	5
10	Abby	Milk	A2	West	MUM	0	10	0
11	Mike	Corn	Maddy	#N/A	#N/A	230	70	0
12	Mike	Cement	Mux	#N/A	#N/A	110	20	0
13	Mike	Roof	Rick	#N/A	#N/A	0	0	30
14
15
16	Name	Zone	Area	Mon	Tue	Wed
17	George	South	TN	20	80	60
18	Abby	West	MUM	0	10	5
19	Mike	#N/A	#N/A	340	90	30
Sheet18

Cell Formulas
Range	Formula
G17:I19	G17	=SUMIFS(I$7:I$13,$D$7:$D$13,$D17,$G$7:$G$13,$E17,$H$7:$H$13,$F17)

Sir,

Thanks, I was trying to fetch unique names and then sum it, (using unique and sum function)

 

[rombo]

Sir,

Thanks, I was trying to fetch unique names and then sum it, (using unique and sum function)

I am able to extract unique names by this formula but not able to sum it,

=UNIQUE(CHOOSE({1,2,3},D7:D13, H7:H13, G7:G13))

 

[Eric W]

What version of Excel are you using? Your member info says Excel 2019, but UNIQUE was not available in 2019. If you actually have Excel 365 (or Excel 2021), then this should work for you:

Book1
	D	E	F	G	H	I	J	K
6	Name	Dept	Job	Zone	Area	Mon	Tue	Wed
7	George	Spices	GA	South	TN	0	40	40
8	George	Spices	GB	South	TN	20	40	20
9	Abby	Milk	A1	West	MUM	0	0	5
10	Abby	Milk	A2	West	MUM	0	10	0
11	Mike	Corn	Maddy	#N/A	#N/A	230	70	0
12	Mike	Cement	Mux	#N/A	#N/A	110	20	0
13	Mike	Roof	Rick	#N/A	#N/A	0	0	30
14
15
16	Name	Zone	Area	Mon	Tue	Wed
17	George	South	TN	20	80	60
18	Abby	West	MUM	0	10	5
19	Mike	#N/A	#N/A	340	90	30
Sheet5

Cell Formulas
Range	Formula
D17:I19	D17	=LET(u,UNIQUE(D7:D13),CHOOSE({1,2,3,4,5,6},u,VLOOKUP(u,D7:G13,4,0),VLOOKUP(u,D7:H13,5,0),SUMIFS(I7:I13,D7:D13,u),SUMIFS(J7:J13,D7:D13,u),SUMIFS(K7:K13,D7:D13,u)))
Dynamic array formulas.

 

[rombo]

What version of Excel are you using? Your member info says Excel 2019, but UNIQUE was not available in 2019. If you actually have Excel 365 (or Excel 2021), then this should work for you:

Book1
	D	E	F	G	H	I	J	K
6	Name	Dept	Job	Zone	Area	Mon	Tue	Wed
7	George	Spices	GA	South	TN	0	40	40
8	George	Spices	GB	South	TN	20	40	20
9	Abby	Milk	A1	West	MUM	0	0	5
10	Abby	Milk	A2	West	MUM	0	10	0
11	Mike	Corn	Maddy	#N/A	#N/A	230	70	0
12	Mike	Cement	Mux	#N/A	#N/A	110	20	0
13	Mike	Roof	Rick	#N/A	#N/A	0	0	30
14
15
16	Name	Zone	Area	Mon	Tue	Wed
17	George	South	TN	20	80	60
18	Abby	West	MUM	0	10	5
19	Mike	#N/A	#N/A	340	90	30
Sheet5

Cell Formulas
Range	Formula
D17:I19	D17	=LET(u,UNIQUE(D7:D13),CHOOSE({1,2,3,4,5,6},u,VLOOKUP(u,D7:G13,4,0),VLOOKUP(u,D7:H13,5,0),SUMIFS(I7:I13,D7:D13,u),SUMIFS(J7:J13,D7:D13,u),SUMIFS(K7:K13,D7:D13,u)))
Dynamic array formulas.

Thanks Sir, its working

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works.
Also do you need to update you profile as that formula does not work with 2019?

 

[rombo]

What version of Excel are you using? Your member info says Excel 2019, but UNIQUE was not available in 2019. If you actually have Excel 365 (or Excel 2021), then this should work for you:

Book1
	D	E	F	G	H	I	J	K
6	Name	Dept	Job	Zone	Area	Mon	Tue	Wed
7	George	Spices	GA	South	TN	0	40	40
8	George	Spices	GB	South	TN	20	40	20
9	Abby	Milk	A1	West	MUM	0	0	5
10	Abby	Milk	A2	West	MUM	0	10	0
11	Mike	Corn	Maddy	#N/A	#N/A	230	70	0
12	Mike	Cement	Mux	#N/A	#N/A	110	20	0
13	Mike	Roof	Rick	#N/A	#N/A	0	0	30
14
15
16	Name	Zone	Area	Mon	Tue	Wed
17	George	South	TN	20	80	60
18	Abby	West	MUM	0	10	5
19	Mike	#N/A	#N/A	340	90	30
Sheet5

Cell Formulas
Range	Formula
D17:I19	D17	=LET(u,UNIQUE(D7:D13),CHOOSE({1,2,3,4,5,6},u,VLOOKUP(u,D7:G13,4,0),VLOOKUP(u,D7:H13,5,0),SUMIFS(I7:I13,D7:D13,u),SUMIFS(J7:J13,D7:D13,u),SUMIFS(K7:K13,D7:D13,u)))
Dynamic array formulas.

Sure will do