> ## Knowledge Base Index
> Fetch the complete knowledge base index at: https://help.carbone.io/sitemap.xml
> Use this file to discover available pages before exploring further.
> Pure-Markdown content can be obtained by appending a '.md' suffix to the content URLs listed in the sitemap (without the trailing slash).

# 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](https://storage.crisp.chat/users/helpdesk/website/f355e91d7615e000/image_4qxrf6.png)

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](https://storage.crisp.chat/users/helpdesk/website/f355e91d7615e000/image_1spz0hf.png)

| **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](https://storage.crisp.chat/users/helpdesk/website/f355e91d7615e000/image_k0ugu9.png)