CODEWITHSUNDEEP
google-sheets
cronobear
cronobear

Reputation: 1

How do you add an adjacent cell value to a sum if the filtered cell is in a list?

Sorry for the confusing title and problem. Here's the example: I'm building a personal finance sheet that returns a table consisting of: Expected pay date, Total bills, Savings, Net income, Pay date, and Bills due.

It pulls from two sources: Bills and Loans

Example Bills Data Source

Debt Item Next Due Frequency
$150 Phone 8 Dec 2023 Monthly
$90 Internet 3 Jan 2024 Monthly
$1900 Insurance 1 Jan 2024 Semi-annually
$850 Half-rent 8 Dec 2023 Bi-weekly
$15 Music 16 Dec 2023 Monthly
$20 Patreon 15 Dec 2023 Monthly

Example Loans Data Source

Debt Item Next Due Frequency
$450 Nissan 15 Dec 2023 Monthly
$300 Credit Card 10 Dec 2024 Bi-weekly
$300 Ford 6 Jan 2023 Monthly
$800 Boat 18 Dec 2023 Monthly

I've gotten it to a point where the expected bills between the previous pay date and the next pay date are shown in a column like so:

Outputted Table

Col A Col B Col C Col D Etc
EXPECTED PAY $3200 $3200 $3200 .
TOTAL BILLS X1 X2 X3 .
SAVINGS SAV_1 SAV_2 SAV_3 .
NET INCOME Y1 Y2 Y3 .
PAY DATE 8 DEC 2023 22 DEC 2023 5 JAN 2023 .
BILLS & LOANS DUE Phone Internet Phone .
. Half-rent Insurance Half-rent .
. Music Half-rent Music .
. Patreon Credit-card Patreon .
. Nissan . Nissan .
. Credit Card . Credit Card .
. Boat . Ford .
. . . Boat .

**Savings **= sum from another data table

Net income = Expected Pay - (Total Bills + Savings)

Total Bills is where I'm struggling to figure out how to add up. This should be the sum of bills listed below in the column. (ex: 8 Dec 2023 would be Phone, Half-rent, Music, Patreon, Nissan, Credit Card, and Boat.) However, I don't know how to add the adjacent Debt cell to the sum Total Bills.

I'm new to sheets and excel, and I'm currently learning python for school. The best method I came up with was to use shove the problem into some Apps Script for the sheet.

I don't know how to approach this problem. I've just been staring at the screen.

Upvotes: 0

Views: 65

Answers (1)

Tedinoz
Tedinoz

Reputation: 7959

You want to calculate Net Pay after expenses by pay period. You have two categories of expenses ("Bills" and "Loans"), and the frequency of payments are:

  • Fortnightly

  • Monthly

  • Six-Monthly

  • The answer consists of three tables:

    • Total
    • billsData
    • loansdata

Notes:

  • "Next Due" is calculated date for each expense.

    • This is unnecessary and complicated given the range of payroll dates that might appear in any given spreadsheet. It is easier to reference an "Anniversary date" so that the application of the expense to a given payroll date can be calculated by formula.

  • The formula is basically a nested IF:

    • if frequency = "Fortnightly", then X else
    • if frequency = "Monthly", then Y, else
    • if frequency = "Six Monthly", then Z

  • the payroll dates in row 1 of billsData and loansData are populated by a query:

    • =query(totals03!B1:Q1,"select *")

  • the calculation formula for billsData is entered in Cell F2:

    • =MAKEARRAY(COUNTA(A2:A),COUNTA(F1:1),LAMBDA(r,c,IF(index(D2:D,r)="Fortnightly",index(A2:A,r),IF(and(index(D2:D,r)="Monthly",date(year(index(F1:1,c)),month(index(F1:1,c)),day(index(C2:C,r)))>=index(F1:1,c),date(year(index(F1:1,c)),month(index(F1:1,c)),day(index(C2:C,r)))<(index(F1:1,c)+14)),index(A2:A,r),if(and(D6="Six-Monthly",date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r)))>=index(F1:1,c),date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r)))<(index(F1:1,c)+14)),index(A2:A,r),if(and(index(D2:D,r)="Six-Monthly",EOMONTH(date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r))),5)+1>=index(F1:1,c),EOMONTH(date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r))),5)+1<(index(F1:1,c)+14)),index(A2:A,r),))))))

  • the calculation formula for loansData is entered in Cell F2:

    • =MAKEARRAY(COUNTA(A2:A),COUNTA(F1:1),LAMBDA(r,c,IF(index(D2:D,r)="Fortnightly",index(A2:A,r),IF(and(index(D2:D,r)="Monthly",date(year(index(F1:1,c)),month(index(F1:1,c)),day(index(C2:C,r)))>=index(F1:1,c),date(year(index(F1:1,c)),month(index(F1:1,c)),day(index(C2:C,r)))<(index(F1:1,c)+14)),index(A2:A,r),if(and(D6="Six-Monthly",date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r)))>=index(F1:1,c),date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r)))<(index(F1:1,c)+14)),index(A2:A,r),if(and(index(D2:D,r)="Six-Monthly",EOMONTH(date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r))),5)+1>=index(F1:1,c),EOMONTH(date(year(index(F1:1,c)),month(index(C2:C,r)),day(index(C2:C,r))),5)+1<(index(F1:1,c)+14)),index(A2:A,r),))))))

  • for sheet = Total, the formula for Bills expenses is entered in Cell B4:

    • =BYCOL(billsData!F2:O,LAMBDA(column,sum(column)))

  • for sheet = Total, the formula for Loans expenses is entered in Cell B5:

    • =BYCOL(loansData!F2:O,LAMBDA(column,sum(column)))

  • for sheet = Total, the formulas for Total Outgoings is a simple Sum, and Net pay is simple formula "Expected Pay" less "Total Outgoings"; these formula are copied across columns.

h/t: @Martin Arrayformula for Index Match multiple columns with date values

Sample - Total

Totals

Sample - Bills data

Bills data

SAMPLE - Loans data

Loans data

Upvotes: 0

Related Questions