How to compute totals in Excel / Spreadsheets when Carbone injects new lines?

Problem

Most spreadsheet reports need totals at the bottom of the table.

Template

But the bottom of the table is dynamically pushed down by Carbone when the data is inserted, so you cannot use a formula =SUM(B4:B5) because B5 is dynamic. It can be B88 or even B351 when Carbone has inserted the data.

Here are two solutions:

Use a dynamic Excel formula

Use the SUM formula combined with INDIRECT and ROW. Here's how it works:

ROW() : This function returns the row number of the cell where the formula is entered.
INDIRECT("B4:B"&ROW()-1) : This formula constructs a range reference that starts at B4 and extends up to the row just above where the formula is applied. The INDIRECT function is used to create a reference to a range of cells specified by a text string. The & is used to concatenate the row number obtained with ROW()-1, thus forming the desired range reference as text.
SUM(INDIRECT("B4:B" & ROW()-1)) : Finally, the SUM function adds up all the values in the range dynamically defined by the INDIRECT formula.

Template formula ROW

Even more robust (a must for bi-directional tables)=> Place this formula in any cell in your template worksheet, and it will produce the sum of all the numbers in the column above that cell:=SUM(INDIRECT(ADDRESS(1;COLUMN())&":"&ADDRESS(ROW()-1;COLUMN())))

Use Carbone aggregators

Carbone can also compute the total itself

{d.action[].time:aggSum:formatN}

Make sure that your Carbone tags end with the formatN formatter to convert numeric values to Excel numbers. Otherwise, Excel formula will not work.

Extra tips

If you want to use your result elsewhere,

first provide in your template a label (ex TOTAL weeks) on which you can build an excel formula with MATCH
use the formula MATCH("TOTAL weeks",A:A,0) to find the relative position of our label in column A,
use INDEX function to combine the search step with a reference to the corresponding cell in column B
then you can perform any computation

exemple : =INDEX(B:B, MATCH("TOTAL weeks", A:A, 0))/4.345

Template Excel MATCH

Updated on: 04/05/2024

Was this article helpful?

Thank you!