Index match lookup multiple column & row headers and sum

B

bbr25

New Member
Joined
Aug 16, 2018
Messages
3
Hi

I have the following table with dates down the rows which is split into 5 activities and the columns are split between 5 desks with a person at that desk:

ABCDEFG
Desk 1Desk 2Desk3Desk 4Desk 5
DateActivityBobJulieDerekJohnDebbie
A
B
C
D
E
DateActivityDerekBobDebbieJulieJohn
A
B
C
D
E
DateActivityDebbieDerekBobJohnJulie
A
B
C
D
E
DateActivityBobDebbieDerekJulieJohn
A
B
C
D
E
DateActivityJohnJulieDebbieDerekBob
A
B
C
D
E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]01/01/2018[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]02/01/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]03/01/2018[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]10/02/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]05/03/2018[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Now, on any specific day any one of those people could sit at any of the desks but the activities remain the same.

What I want is a summary sheet that sums all activities per person by month:

ABCDEFG
MonthActivityBobJulieDerekJohnDebbie
A
B
C
D
E
A
B
C
D
E
A
B
C
D
E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]January[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]February[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]March[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​



Is this possible using formulas in each of the cells in Sheet 2 or does it need VBA?

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You must log in or register to reply here.

Similar threads

B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
614
Peter_SSs
Peter_SSs
S
  • Solved
Mix of info in a column that needs to be specific in a given month, but matches the final criteria for a total.
Replies
4
Views
73
Fluff
Fluff
L
  • Solved
Help Please
2 3
Replies
21
Views
615
Asbestos_Jen
Asbestos_Jen
J
  • Question Question
Populate a range of cells to end up with a given total, Solver? Formula?
Replies
3
Views
102
NdNoviceHlp
NdNoviceHlp
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
620
Alex Blakenburg
A

Forum statistics

Threads
1,225,487
Messages
6,185,270
Members
453,285
Latest member
Wullay

Share this page

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