Share via

Progressive total by using table structured refeerences

Anonymous
2023-10-01T07:01:38+00:00

Hi,

I trying to use the following formula for progressive total from Jan to Feb, Jan To Mar, Jan to Apr, Jan to May and Jan to June however when I drag the formula result remain the same i.e. 418 for Jan.

Please guide to rebuild the formula for correct progress total i.e. 418, 880, 1345, 1817, 2234 and 2621.

=SUM(Table3[[#Totals],[Jan ]:[Jan ]])

Sample data

Employee Jan Feb Mar Apr May Jun
A 100 150 120 110 105 100
B 90 95 115 120 102 99
C 110 105 125 150 115 85
D 118 112 105 92 95 103
Total 418 462 465 472 417 387
Progressive Total 418 418 418 418 418 418
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

riny 20,870 Reputation points Volunteer Moderator
2023-10-01T08:55:03+00:00

I tried Hans' formula and it worked for me. You can try this one as well:

Formula in B8, copied across:

=SUM(Table3[[#Totals],[Jan]:[Jan]]:Table3[[#Totals],[Jan]])

Was this answer helpful?

3 people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-01T08:09:24+00:00

    Hi

    I have used the following formula however it is showing error message #REF!, Please guide.

    =SUM(OFFSET(INDIRECT(Table3[[#Totals],[Employee]]),0,0,1,MATCH(Table3[[#Headers],[Jan ]],Table3[#Headers],0)))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points
    2023-10-01T07:39:46+00:00

    For the first progressive total:

    =SUM(OFFSET(INDIRECT("Table3[[#Totals],[Employee]]"),0,0,1,MATCH(Table3[[#Headers],[Jan]],Table3[#Headers],0)))

    Fill to the right.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments