Calculating the sum of a dynamic column

[Maverick]

Hi,

My first post here so thanks for the platform.

Im looking to sum 'total 213' and divide by sum 'total 2014' to get a % change.

However, the headers '2013 total' and '2014 total' are dynamic so the totals could be at the end of Jan-Mar or at the end of Jan-Dec..

ie

Jan Feb Mar 2013 Total Jan Feb Mar 2014 Total
50 5 10 65 5 10 60 75
20 2 2 24 10 50 80 140
70 7 12 89 15 60 140 215

In this example, the answer would be 141.5%


Further, the grand total total 2013 & 2014 could have 10 or 100 rows..

Hope thats clear and hope further you can help!

Many Thanks in advance

 

[Faseeh]

Hi Mavrick,

Please see this file..

 

[Jon von der Heyden]

Assumptions:

1. Headers are in row1
2. Data is is A:H (extend as necessary)

To get 2013 total:
=INDEX(INDEX($A:$H,0,MATCH("2013 total",$A$1:$H$1,0)),MATCH(10^308,INDEX($A:$H,0,MATCH("2013 total",$A$1:$H$1,0)),1),1)

To get 2014 total:
=INDEX(INDEX($A:$H,0,MATCH("2014 total",$A$1:$H$1,0)),MATCH(10^308,INDEX($A:$H,0,MATCH("2014 total",$A$1:$H$1,0)),1),1)

You can refer "2013 total" and "2014 total" to two cells on the grid.

 

[Maverick]

That is simply incredible. Thank you both so much I went with Jon's solution as it captured the dynamic nature of the task.
Jon, could I just ask, what exactly the '10^308' part of the formula does?

Thank you both again; you've helped immensely in ensuring the final part (and key component)of an epic project works and now looks great. Not to mention saving me a lot of time and a headache!

 

[Jon von der Heyden]

The 10^308 is simply a really large number that many of us use as a convention for performing binary search on an array of numbers, and hence ensure it always matches the last number in the array.

I explain how binary search works in Excel here.

 

[Maverick]

Thanks Jon, that's very insightful.