Calculate the last cell as oppose to the first cell

[CMACY]

Good day I am trying to calculate some data in excel and I urgently need some assistance in getting the formula to work well.

Issue:

A1 B1

80 100% 96% 80%
60 69% 50% 50%
50 70% 40% 20%
63 55% 30%
50 69%

So based on the example above, I am trying to sum the data in column A while dragging across other columns. So in other words. in cell A15 I should have (sum(50+63+50+60+80), then in B15 i should have (sum(63+50+60+80), then in c15 i should have sum(50+60+80).

So i need the formula to work that way, whereby it sums from the bottom up and drops the last cell in column A as it goes across.

Let me know if this not clear. I appreciate any help available.

 

[KMacauley]

Hi CMacy,

I don't know if this is the most elegant solution, but it works.

=SUM(INDIRECT("A1:A" & COUNT($A$1:$A$14) - COLUMN() + 1))

You might have to modify your ranges.


Kim.

 

[CMACY]

Hi CMacy,

I don't know if this is the most elegant solution, but it works.

=SUM(INDIRECT("A1:A" & COUNT($A$1:$A$14) - COLUMN() + 1))

You might have to modify your ranges.


Kim.

Good day Kim, Thank you for your assistance. I tried the formula you presented but it produce different results than I needed. I am trying to have the formula sum from the last last cell upwards.

 

[XOR LX]

Hi,

Assuming your original data is in A1:A5:

=SUM($A$1:INDEX($A$1:$A$5,ROWS($A$1:$A$5)-COLUMNS($A:A)+1))

Copy across as required.

Regards

 

[CMACY]

Hi,

Assuming your original data is in A1:A5:

=SUM($A$1:INDEX($A$1:$A$5,ROWS($A$1:$A$5)-COLUMNS($A:A)+1))

Copy across as required.

Regards

Hi,

Excellent! That works perfectly!

 

[XOR LX]

You're welcome!