CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulasumifs
Gabe Minson
Gabe Minson

Reputation: 1

how to use google sheets formulas to add numbers based on/only if within a specific two week time period?

I have a very extensive budget that i've been slowly expanding the functions of. There are multiple pages for calculating:

The page im currently working on is the actual BUDGET sheet: a mostly-autofilling sheet that has ALL the information from each of the others.

This budget sheet autopopulates my estimated check, anticipated costs, weekly allowance, general savings, and sinking funds, from other sheets within the larger one. From these autopopulated amounts comes my "fun bun mon," which is effectively a discretionary fund.

The actual math I do to get to this amount is:

I have a sheet for other income, but it is not sorted by pay periods like my other information; it's just a table with the total amount of transactions that have been made.

(P+(P-E)+O)-(C) = fun bunny money

Sample data

I do it this way because I like to underestimate my total paycheck amount while budgeting- i'm a super spender, so having an estimation that i budget from and having the extra be "fun money" is just a way to trick my brain. Other income is also "extra money" in my mind- but I only want the other income from WITHIN the pay period timespan to be put in the budget, so that it is accurate to date. here is where the issue lies !! :(

I have only had one successful formula that actually pulls the correct other income amount for the pay period, that being cell M19 :

=SUMIFS('other in'!D6:D,'other in'!C6:C,">=2024-04-21",'other in'!C6:C,"<=2024-05-04")

my Other in page looks like

and it's just so long!! I don't want to have to auto-enter the date for each individual pay period, it would be a blessing if i could just calculate it somehow based on my pay date- the pay period start date is 19 days before the payday, and the end is 6 days before.

I'm genuinely just at a loss on how to do this- I REALLLLY struggle with array formulas, so all the advice i've seen on that that hasn't been specific to my situation has been really hard for me to translate over into what i can do for myself.

I attempted learning/using the workdays function in order to reference the pay period based on the given pay date within the budget, but struggled- it didn't work. this is as far as I got with it; see cell M18:

=SUMIFS('other in'!D5:D,'other in'!C5:C,">=(WORKDAY(C18,-14))",'other in'!C5:C,"<=(WORKDAY(C18,-5))")

I attempted adding a pay period beginning/end section before the date, which I thought the formula could possibly pull from so it isn't so clunky, but it also didn't formulate correctly; see cell M17:

=SUMIFS('other in'!D5:D,'other in'!C5:C,">=C17",'other in'!C5:C,"<=$D$17")

I also tried the EDATE function, but it only processes months, and only allows full integers, so that formula also didn't work, in cell M22:

=SUMIF('other in'!C5:C,EDATE(E22,-0.5),F5:F)

Upvotes: 0

Views: 27

Answers (1)

E.Wiest
E.Wiest

Reputation: 5915

Well, you can use something like this :

=MAP(B4:B7;LAMBDA(X;QUERY(F3:H8;"SELECT SUM(Col3) WHERE Col2 >= DATE "&"'"&TEXT(X-19;"yyyy-MM-dd")&"'"&" AND Col2 <= DATE "&"'"&TEXT(X-6;"yyyy-MM-dd")&"'"&" LABEL SUM(Col3)''")))

Budget

B4:B7 contain all of your pay dates. For each one (MAP), you'll calculate with QUERY (used in a LAMBDA function) the sum of other income profit rows regarding the following timespan : [Paydate-19 days;Paydate-6days]. Col2 = date column; Col3 = amount.

In the preceding screenshot, for the first pay date (2024-07-08), we look for rows in "Other income" sheets with the following criteria : date >= to 2024-06-19 and date <= to 2024-07-02 to respect your criterias. The result is the sum of line 1 and 2 : 10 + 20 = 30.

Additional notes :

  • Be sure the column containing the dates in "Other income" sheet is correctly specified ("Date").
  • Since I'm located in Europe, ";" should be replaced with ",".

Upvotes: 0

Related Questions