google-sheets - Google Sheets - add all values until last key is found

Question

I have a sheet where I need to add all the values from last interest date till current interest date. How can I do that ?

For example, here in the above sheet I need to calculate the interest charged on 01-Feb-2021 which is sum of $580.15 and $952.91 (in turn it is adding all interest values until last Interest keyword is found in 2nd column - which is $1533.06)

How do I calculate it automatically by looking at the previous and current Interest keywords in 2nd column.

标签： google-sheets

Answer 1

add all values until last key is found

try:

=QUERY(B3:H, "select B,sum(H) where B is not null group by B label sum(H)''")

---

update:

=ARRAYFORMULA(SUM(INDIRECT(
 ADDRESS(LARGE((B3:B="interest")*ROW(B3:B), 2)+1, 8)&":"&
 ADDRESS(  MAX((B3:B="interest")*ROW(B3:B)), 8))))