What do you do if you have a list and you constantly have to add the new records to the grand total using the SUM formula? If your SUM formula is immediately below the record, you will have to keep changing your range as the SUM formula would not include the new row added before the grand total row.

One work around is to add in an empty row and include that in your SUM formula. Whenever there is a need to add new records, add it before this blank row so that the SUM formula will always include the new records in the SUM formula.

What if you are not allowed to keep a blank row for this purpose? Today, I am going to show you a new solution.

Assuming that the SUM formula is in A11 and is currently adding the numbers in the range A1:A10. Instead of creating a SUM formula that simply adds up the range A1:A10 [=SUM(A1:A10)], create the range in text format and use the ROW formula. The range including the inverted commas becomes “A1:A”&Row()-1. The ROW()-1 will always calculate one row before the SUM formula. Since this is a text and cannot be read as a range, you need to the INDIRECT formula to convert the text based range into a proper range. Adding the INDIRECT and SUM formula, you new SUM formula should be =SUM(INDIRECT(“A1:A”&ROW()-1)). This new SUM formula will save you the time to change the range constantly and you will not have to live with an empty row between your data and the grand total anymore.

Leave a Reply