Hi Macropod
You are right, your formula does, in fact, work perfectly for the problem posed by OP. Aladin's comment means that you can improve it. In this case in 2 ways: make it robust and non-volatile.
- It's not robust. This means that it's dependent on the specific position of the data.
Ex.: Suppose you want to add some info before the table. If you select row 1 and insert some rows to write that information, your formula will no longer work, because it was dependant on the table starting in row 2. Or if you decide to have the summary elsewhere and select the formulas in E2:E7K and move it to start in, for ex., F5. The formulas will stop working for the same reason. If you try Aladin's formula you find that it's indiferent to you moving the data around.
- It uses volatile functions. As you know some formulas are always recalculated every time the worksheet calculates. Offset() and Row() are 2 of them as are Now() or Rand(). This means that if, for ex., you have in the sheet a formula like =H1+1, each time you change H1, the worksheet calculates, and your 7K+ formulas will also recalculate although they may have nothing to do with it.
Best regards
PGC