(Please edit your original question and append the "above" to the subject if conjecture '0.' is correct.)
Having had a look into the sample I need to know if the original questioner probably
0. actually means "...then adds the numbers
above it until the next blank cell.
1. wants to get subtotals per date. (column B)
2. insists on spliting columns by blank rows and assures to have done so for every section. (Originally supposed to be bad style.)
3. intends to scale up the sheet to thousands of rows. (Efficiency!)
4. needs a way to only calculate the sum for last rows of any section delimited by empty rows.
5. is considering to omit overblown formatting. (Simpler often is clearer and gives good survey.)
If "no" to 1., "yes" to 2., and "no" again to 3.
- Code: Select all Expand viewCollapse view
=SUMPRODUCT(OFFSET(I30;MAX(ROW(I$1:I30)*(I$1:I30=""))-ROW(I30)+1;0;ROW(I30)-MAX(ROW(I$1:I30)*(I$1:I30=""));1))
is a solution without a macro. Of course, the subexpression
- Code: Select all Expand viewCollapse view
MAX(ROW(I$1:I30)*(I$1:I30=""))-ROW(I30)
should be outsourced for array evalution to a helper cell for the sake of efficiency, anyway.
Assuming the helper cell is P30, the original formuala would alter to
- Code: Select all Expand viewCollapse view
=SUMPRODUCT(OFFSET(I30;P30+1;0;-P30;1))
An example about how I would do it (under the already mentioned set of conditions + "yes" to 4.) is attached.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München