Use a variable as a sum factor.

Holst

New Member
Joined
May 24, 2012
Messages
1
Hi Mr Excel
I run a small business, where I have a few employees.
I want to create a timesheet, where the weeks are divided into seperate sheets and in the end i have a aggregated sheet.
I need a formula that can aggregate all x, y and z seperately from the week into my aggregated sheet.

i will try to illustrate my problem below

Customer Monday Tuesday Wed .... Aggregated
1001 4 2 1 7
1002 3 2 3 8
1001 1 1 1 3


Aggregated for: 1001 = 10
1002 = 8
I need a formula to aggregate all 1001, 1002 etc..
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
Maybe this way.
Excel Workbook
ABCDE
1CustomerMondayTuesdayWedAggregated
210014217
310023238
410011113
5
6
7
8100153210
910023238
Blad1
Cell Formulas
RangeFormula
B8=SUMPRODUCT(($A$2:$A$6=$A8)*(B$2:B$6))
B9=SUMPRODUCT(($A$2:$A$6=$A9)*(B$2:B$6))
C8=SUMPRODUCT(($A$2:$A$6=$A8)*(C$2:C$6))
C9=SUMPRODUCT(($A$2:$A$6=$A9)*(C$2:C$6))
D8=SUMPRODUCT(($A$2:$A$6=$A8)*(D$2:D$6))
D9=SUMPRODUCT(($A$2:$A$6=$A9)*(D$2:D$6))
E8=SUMPRODUCT(($A$2:$A$6=$A8)*(E$2:E$6))
E9=SUMPRODUCT(($A$2:$A$6=$A9)*(E$2:E$6))
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Try in B8 dragged across and down: =SUMIF($A$2:$A$4,$A8,B$2:B$4)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,122
Messages
5,570,313
Members
412,318
Latest member
angoeyuan
Top