Google Sheets Sum a column if start date or start date + multiple of frequency column

Question

I have 5 Columns in google sheets

A - Date, shows every days date for 365 days in 2023 (snipped to 15 here)

B - Where the calculations will go

C - $ Amount

D - Start Date

E - Frequency in Days

Column B needs to contain a sum of all values in column C if the date in column D is the same as A OR is the date plus a multiple of the frequency in column E.

	A	C	D	E
1	1/1/2023	24	1/1/2023	2
2	1/2/2023	234	3/3/2023	4
3	1/3/2023	345	1/5/2023	3
4	1/4/2023	124	1/10/2023	5
5	1/5/2023
6	1/6/2023
7	1/7/2023
8	1/8/2023
9	1/9/2023
10	1/10/2023
11	1/11/2023
12	1/12/2023
13	1/13/2023
14	1/14/2023
15	1/15/2023

Can this be done? I have experimented with many options and chatGPT which doesn't understand the issue as I try to present it.

Edit: perhaps my approach is incorrect and this is my first post so apologies. I am trying to generate a cashflow planner of sorts.

The first column is just the dates for the year. Second column is where the magic happens, third column (which I didn't include because that formula is easy) is a running total or balance considering income or expenditure.

The other three columns will be start date, frequency, amount and weather its income or expenditure.

So the process will be to add say income, lets say its $1000 every week, you'd add $1000, frequency 7, type income.

The calc column would add 1000 every week from the start date. The same would be true for expenses. SUMIFS work for the start date but only go so far!

Bonus points for the ability to add a one off (ie no frequency) income/expendature!

Thanks

Prepare a file and share it. The way your question is above, you won't get ay help. — onit, Jun 28 '23 at 13:20
Please make sure the code is in the post and not in a link. Also, no images :) — Vega, Jun 28 '23 at 13:23

P.b · Answer 1 · 2023-06-29T10:49:53.167

0

If I understood it correctly this would work in Excel:

=MMULT((TOROW(D1:D4)=A1:A15)+(TOROW(D1:D4+E1:E4)=A1:A15),C1:C4)

This will sum all values that equal the date in column D or equal the date + the value in column E.

Or more dynamic:

=LET(a, TOCOL(A:A,1),
     c, TOCOL(C:C,1),
     d, TOROW(D:D,1),
     e, TOROW(E:E,1),
MMULT((d=a)+(d+e=a),c))

With the edit made in your post it becomes a lot more complicated. But you end your post that your end goal is to get the total.

This formula will sum all values from column C starting from the day mentioned in D with a repeating interval as mentioned in E up until the maximum date from A:

=LET(a,TOCOL(A:A,1),
     c,TOCOL(C:C,1),
     d,TOROW(D:D,1), 
     e,TOROW(E:E,1),
SUM(
    MAP(   TOROW(c), d, e,
    LAMBDA(w,        x, y,
       LET(z,SEQUENCE(,(y+MAX(a)-x)/y,x,y),
    SUM(
        MMULT(--(z=a),EXPAND(w,COUNTA(z),,w))))))))

(Formula used in F1:)

This screenshot may best explain what it sums:

Formula used for that: =LET(a,A1:A15,c,C1:C4,d,TOROW(D1:D4),e,TOROW(E1:E4),--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,(MAP(TOROW(c),d,e,LAMBDA(w,x,y,LET(z,SEQUENCE(,(y+MAX(a)-x)/y,x,y),TEXTJOIN("+",,MMULT(--(z=a),EXPAND(w,COUNTA(z),,w)))))))),"+",";")))

edited Jun 29 '23 at 10:49

answered Jun 28 '23 at 13:54

P.b

8,293
2
10
25

Thankyou so much for your input, not sure if you used excel online or not but Google Sheets threw an error about the array sizes not being the same. You gave me some ideas to try though with a different approach, Grateful thankyou. One small point too, it needs to be a multiple of column E so for example starts at a certain date but is added to the cell every 3 days. In your example you added the amount and then once more at the specified number of days. I can see how that would be confused from my poor description though. – Christopher Allan Jun 29 '23 at 04:02
See edit to cope with repeating interval. Replacing full column references to actual ranges may help prevent the error you received. (`TOCOL(range,1)` means exclude all blanks in the range and make it transpose to a column. If you have another value somewhere else in the range, it would get added in the calculation). `=LET(a,A1:A15,c,C1:C4,d,TOROW(D1:D4),e,TOROW(E1:E4),SUM(MAP(TOROW(c),d,e,LAMBDA(w,x,y,LET(z,SEQUENCE(,(y+MAX(a)-x)/y,x,y),SUM(MMULT(--(z=a),EXPAND(w,COUNTA(z),,w))))))))` would be the actual range reference, where `TOROW` is used to transpose from column to row. – P.b Jun 29 '23 at 09:09
By the way I used Excel (Office 365 for Android in this case) – P.b Jun 29 '23 at 17:59

Google Sheets Sum a column if start date or start date + multiple of frequency column

1 Answers1