• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

constant formula

K

Katina McDaniel

New Member
Hello. My name is Katina McDaniel. I am new to this forum. I am very excited about learning more from the pros!
My problem is this. I have a list of data which are numbers. I change the data every day and I need the formula to stay the same even though I may change or delete the cell data. For example I tried this =sum(indirect($a1:$a50)) however that is not working. Any advice? Thanks.
 
Katina

Firstly, Welcome to the Chandoo.org Forums

What about something like
=Sum(Offset($A$1,,,Count($A:$A)))
 
Okay. Forgive me I am not as versed in excel. I would like to post the workbook would you take a look and maybe that would explain the question.
 
Hui said:
Katina

Firstly, Welcome to the Chandoo.org Forums

What about something like
=Sum(Offset($A$1,,,Count($A:$A)))
Click to expand...
I have posted the workbook maybe that would help with understanding my question. Thank you again.
 

Attachments

  • sample scojac checks rec do not use.xlsx
    19.6 KB · Views: 4
Katina,

There’s a simple formula to sum all the values in Column B:

=SUM(B:B)

HOWEVER, if you enter this formula into the same column (Column B), you will create a circular reference, which is problematic.

If it is okay with you to display the TOTAL in a separate column, this formula is probably the simplest answer.

If you want to include multiple columns in the sum, just separate them with commas:

=SUM(B:B,E:E,H:H)
 
To create a dynamic formula using the INDIRECT function that you suggested, things will be a more complicated. This formula will sum all the numbers in column B from row B5 to the last row above your sum:

=SUM($B$5:INDIRECT("B"&ROW()-1))

In order to sum multiple columns, you would simply have to duplicate the “inside” of this SUM formula, separating with commas and changing the column references:

=SUM($B$5:INDIRECT("B"&ROW()-1),$E$5:INDIRECT("E"&ROW()-1),$H$5:INDIRECT("H"&ROW()-1))

This formula can live at the bottom of your table in any column: you can paste it right into the sample file you provided.
 
Last edited:
Hi Katina ,

You were on the right track with INDIRECT , which is almost the only function which is impervious to row / column deletions.

Hui is right that if you put the total above the header row , you can do anything below it , and the formula at least will remain.

If you combine both techniques , you can use something like this :

=SUM(INDIRECT("$A$5:$K$100")*(INDIRECT("$A$4:$K$4")="Amount"))

This will have to be entered as an array formula , using CTRL SHIFT ENTER.

You can delete rows and columns to your heart's content , provided this formula is entered where its row / column will not be deleted.

Narayan
 
Okay. I have tried this however when I delete the data in A60 or move the data to another cell it changes the formula total. I want the formula to always sum the data (ex a1:a60,e1:e60,h1:h60,k1:k60) in those cells regardless of whether I change the cell data or move the cell data to another cell. Does this make sense? Thank you in advance for bearing with me.
 
NARAYANK991 said:
Hi Katina ,

You were on the right track with INDIRECT , which is almost the only function which is impervious to row / column deletions.

Hui is right that if you put the total above the header row , you can do anything below it , and the formula at least will remain.

If you combine both techniques , you can use something like this :

=SUM(INDIRECT("$A$5:$K$100")*(INDIRECT("$A$4:$K$4")="Amount"))

This will have to be entered as an array formula , using CTRL SHIFT ENTER.

You can delete rows and columns to your heart's content , provided this formula is entered where its row / column will not be deleted.

Narayan
Click to expand...

Thank you. When I pasted =SUM(INDIRECT("$A$5:$K$100")*(INDIRECT("$A$4:$K$4")="Amount")) in my workbook the result was #VALUE! how do I correct that? Thank you once again.
 
You must log in or register to reply here.
Back
Top