Sum and display cells based on unique values of a column

rombo

Board Regular
Joined
Aug 18, 2010
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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.

NameDeptJobZoneAreaMonTueWed
GeorgeSpicesGASouthTN04040
GeorgeSpicesGBSouthTN204020
AbbyMilkA1WestMUM005
AbbyMilkA2WestMUM0100
MikeCornMaddy#N/A#N/A230700
MikeCementMux#N/A#N/A110200
MikeRoofRick#N/A#N/A0030
Need Formula to get below table from above table
NameZoneAreaMonTueWed
GeorgeSouthTN
20​
80​
60​
AbbyWestMUM
10​
5​
Mike#N/A#N/A
340​
90​
30​
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I think this might do it.

MrExcel posts20.xlsx
DEFGHIJK
6NameDeptJobZoneAreaMonTueWed
7GeorgeSpicesGASouthTN04040
8GeorgeSpicesGBSouthTN204020
9AbbyMilkA1WestMUM005
10AbbyMilkA2WestMUM0100
11MikeCornMaddy#N/A#N/A230700
12MikeCementMux#N/A#N/A110200
13MikeRoofRick#N/A#N/A0030
14
15
16NameZoneAreaMonTueWed
17GeorgeSouthTN208060
18AbbyWestMUM0105
19Mike#N/A#N/A3409030
Sheet18
Cell Formulas
RangeFormula
G17:I19G17=SUMIFS(I$7:I$13,$D$7:$D$13,$D17,$G$7:$G$13,$E17,$H$7:$H$13,$F17)
 
Upvote 0
I think this might do it.

MrExcel posts20.xlsx
DEFGHIJK
6NameDeptJobZoneAreaMonTueWed
7GeorgeSpicesGASouthTN04040
8GeorgeSpicesGBSouthTN204020
9AbbyMilkA1WestMUM005
10AbbyMilkA2WestMUM0100
11MikeCornMaddy#N/A#N/A230700
12MikeCementMux#N/A#N/A110200
13MikeRoofRick#N/A#N/A0030
14
15
16NameZoneAreaMonTueWed
17GeorgeSouthTN208060
18AbbyWestMUM0105
19Mike#N/A#N/A3409030
Sheet18
Cell Formulas
RangeFormula
G17:I19G17=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)
 
Upvote 0
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))
 
Upvote 0
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
DEFGHIJK
6NameDeptJobZoneAreaMonTueWed
7GeorgeSpicesGASouthTN04040
8GeorgeSpicesGBSouthTN204020
9AbbyMilkA1WestMUM005
10AbbyMilkA2WestMUM0100
11MikeCornMaddy#N/A#N/A230700
12MikeCementMux#N/A#N/A110200
13MikeRoofRick#N/A#N/A0030
14
15
16NameZoneAreaMonTueWed
17GeorgeSouthTN208060
18AbbyWestMUM0105
19Mike#N/A#N/A3409030
Sheet5
Cell Formulas
RangeFormula
D17:I19D17=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.
 
Upvote 0
Solution
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
DEFGHIJK
6NameDeptJobZoneAreaMonTueWed
7GeorgeSpicesGASouthTN04040
8GeorgeSpicesGBSouthTN204020
9AbbyMilkA1WestMUM005
10AbbyMilkA2WestMUM0100
11MikeCornMaddy#N/A#N/A230700
12MikeCementMux#N/A#N/A110200
13MikeRoofRick#N/A#N/A0030
14
15
16NameZoneAreaMonTueWed
17GeorgeSouthTN208060
18AbbyWestMUM0105
19Mike#N/A#N/A3409030
Sheet5
Cell Formulas
RangeFormula
D17:I19D17=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
 
Upvote 0
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?
 
Upvote 0
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
DEFGHIJK
6NameDeptJobZoneAreaMonTueWed
7GeorgeSpicesGASouthTN04040
8GeorgeSpicesGBSouthTN204020
9AbbyMilkA1WestMUM005
10AbbyMilkA2WestMUM0100
11MikeCornMaddy#N/A#N/A230700
12MikeCementMux#N/A#N/A110200
13MikeRoofRick#N/A#N/A0030
14
15
16NameZoneAreaMonTueWed
17GeorgeSouthTN208060
18AbbyWestMUM0105
19Mike#N/A#N/A3409030
Sheet5
Cell Formulas
RangeFormula
D17:I19D17=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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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