Automatically expanding SUMPRODUCT range according to DATE

[koyotak]

One or more items can be recorded per day, as the position number POS.# indicates. The daily total value is output using the SUMPRODUCT() function. I have to enter this (both column ranges) manually so far because the number of rows varies, which is reflected in the first decimal place in POS.#. If there is only one item in the day, only the consecutive INTEGER is output as the position POS.#.

I am failing at the task of automating the daily total value in column [I:I] to the extent that I only connect the row-by-row daily number of cells [I] to a pseudo-cell with a customized SUMPRODUCT() output. One solution may seem luxurious, but it avoids typical input errors that are not immediately apparent.

My very first, simplest formula was tactilely stupid and much less error-prone: (n×a + n×b + n×c …) × (%) + postage, until I discovered SUMPRODUCT().

000_LO-CALC_SUMPRODUCT_v0000_082215.ods (22.8 KB)

[mariosv]

I think is what you are asking for:
=IF($B11<>$B12;SUMPRODUCT($D$9:$D11;$E$9:$E11;$B$9:$B11=$B11)*(1+$F$7)+$H11;"")

[Lupp]

I don’t think the intended usage of SUMPRODUCT() is recommendable.
For sheets with very many rows it may be inefficient.
Consider to check the attached suggestion.
disask_126695_suggestion.ods (33.8 KB)

[koyotak]

1. @mariosv
2. @Lupp

I like both algorithms!

Regarding 1): The fact that the SUMPRODUCT() function, as a matrix structure, begins with a fixed referenced cell and ends with a variable cell is the desired solution, but I’m not familiar with it, and its internal logical structure remains unclear to me. In principle, it’s ingenious, but I have to know and understand this structure.

Regarding 2): In principle, I prefer this solution with easy-to-control auxiliary columns [R:T], respective [M]! Less replacing them with far too complex formulas in [B]. However, [R:T] is more of a secondary algorithm.
The primary algorithm: The auxiliary column [M] with successive additions up to the limit before the next day; I’ll use that for my current spreadsheet.

Regarding 2): Why is SUMPRODUCT() less efficient? As a test, I expanded both files by 1000 rows, whereby the one 1) with SUMPRODUCT() was only half as large (145 KB) as the more extensive 2) without SUMPRODUCT() (290 KB).

Conclusion: 1) is the formula I am looking for and 2) the solution to be applied.