How to pick up the last total in a column of totals which continually gets added to

Question

I have two sheets. First one is a list of transactions. Second sheet keeps a running total of the last total. This total naturally changes as more transactions are added.

So I am looking for a formula in the summary sheet that picks up the last total of the transaction sheet

Thanks

Answer 1

To pick up the last total in a column of totals in Excel, you can use the LOOKUP function or the INDEX and MATCH combination. Here’s how you can do it:

Using LOOKUP Function

Assuming your totals are in column A of the transactions sheet, you can use the following formula in your summary sheet:

=LOOKUP(2,1/(Sheet1!A:A<>""),Sheet1!A:A)

This formula works by looking for the last non-empty cell in column A of the transactions sheet (named "Sheet1" in this example).

Using INDEX and MATCH

Alternatively, you can use:

=INDEX(Sheet1!A:A, MATCH(1E+100, Sheet1!A:A))

This formula finds the last numeric value in column A.

Make sure to replace "Sheet1" with the actual name of your transactions sheet if it's different. These formulas will automatically update as you add more transactions, ensuring that you always get the latest total.

---

References:

• Excel performance: Tips for optimizing performance obstructions

Answer 2

Hi,

Try this

=xlookup(true,Sheet1!A:A<>"",Sheet1!A:A,,,,-1)

Answer 3

=INDEX(Sheet1!A:A, MATCH(1E+100, Sheet1!A:A))

This worked perfectly. Thank you