### Excel formula – Miscellaneous

I recently had to work with Excel formulas, and I encountered a small challenge. The problem was this: I had a table with variating height (the user could add and delete rows), that was likely to sometimes have 0 rows.Also, it wasn’t just the sum of the whole column I needed, as the SUM value should be at the bottom of the table, and a circular loop should be avoided. Therefore, the usual SUM() didn’t work.

I did not want to use VBA for this problem, so I turned to this solution:

Example: Column A: Names – Column B: Salary

1) Find the last cells that isn’t empty (in column A).
2) Make a SUM()-formula in column B that can take the row number from 1) as a parameter.

1) (Danish)

```=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003))))
```

(English)

```=(SUMPRODUCT(MAX((A4:A65003<>"")*ROW(A4:A65003))))
```

To make the solution understandable, I will enter the above formula in cell G1. Now, I have defined the last row(G1), and I just need a fixed first row, which I define as 2: I need to use the SUM() and the INDEKS() to sum the variable amount of rows:

(Danish)

```=SUM(INDEKS(B:B;2):INDEKS(B:B;G1))
```

(English)

```=SUM(INDEX(B:B;2):INDEX(B:B;G1))
```

Of course, put togther, it looks like this:

```=SUM(INDEKS(B:B;2):INDEKS(B:B;(=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003)))) )))
```
Wednesday, December 8th, 2010 Excel

May 2021
M T W T F S S
« Feb
12
3456789
10111213141516
17181920212223
24252627282930
31