### 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)))) )))

M | T | W | T | F | S | S |
---|---|---|---|---|---|---|

1 | 2 | 3 | 4 | |||

5 | 6 | 7 | 8 | 9 | 10 | 11 |

12 | 13 | 14 | 15 | 16 | 17 | 18 |

19 | 20 | 21 | 22 | 23 | 24 | 25 |

26 | 27 | 28 | 29 | 30 |

#### Recent Posts

- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet