Excel/Spreadsheet Formula Help: Splitting and Reimbursing Shared Expenses

I have a monthly expenses tracking spreadsheet with two sheets: "Expenses Summary" → Contains a list of members and columns for "To Pay" (how much they owe) and "To Receive" (how much they should be reimbursed). "Jan 2025" (monthly expenses sheet) → Logs expenses with the following columns: B: Date C: Amount D: Category E: Spent By (Dropdown: Anlo, Vira, Ken) F: Description Each person pays for some expenses throughout the month, but all expenses are meant to be shared equally among the three members. Goal: For each person in the "Expenses Summary" sheet, I need formulas to calculate: "To Pay" → How much a person owes because they benefited from shared expenses paid by others. A person should contribute 1/3 of each expense that they did not pay themselves. "To Receive" → How much a person should be reimbursed because they paid more than their share. This is the total amount they paid minus their own fair share (1/3 of every expense). Example Data from "Jan 2025" Sheet: Date Amount (C) Category Spent By (E) Description Jan 3 3,790.50 Food Vira Groceries Jan 3 2,369.00 Food Vira More groceries Jan 3 315.00 Utilities Ken Gas bomb Jan 20 555.00 Food Vira Shopping Jan 20 106.00 Food Ken Pet food Jan 24 75.00 Food Ken Milk powder Jan 24 50.00 Food Ken Soup meat Jan 24 137.50 Food Vira Larger milk pack Jan 24 100.00 Food Vira Smoked sausage Jan 27 4,959.50 Food Vira Grocery store Jan 27 937.50 Food Vira Supermarket Jan 27 406.88 Electricity Vira Jan 27 94.50 Water Vira SWM Jan 27 673.46 Internet Vira Telesur Expected Output in "Expenses Summary" Sheet: Members To Pay To Receive Anlo 4,856.61 0 Vira 182.00 9,349.23 Ken 4,492.61 364.00 Current Formula Attempts (Not Working): For "To Pay": =SUMPRODUCT( (INDIRECT(TEXT($B$4,"mmm yyyy")&"!C3:C1000") / 3) * (INDIRECT(TEXT($B$4,"mmm yyyy")&"!E3:E1000") A14) ) For "To Receive": =SUMIF( INDIRECT(TEXT($B$4,"mmm yyyy")&"!E3:E1000"), A14, INDIRECT(TEXT($B$4,"mmm yyyy")&"!C3:C1000") ) - B14 Both formulas return errors or incorrect values. What I Need: A working formula for "To Pay" that correctly sums 1/3 of every expense they did not pay. A working formula for "To Receive" that calculates the amount they overpaid and should get reimbursed. Any help is greatly appreciated!

Mar 19, 2025 - 03:54

I have a monthly expenses tracking spreadsheet with two sheets:

"Expenses Summary" → Contains a list of members and columns for "To Pay" (how much they owe) and "To Receive" (how much they should be reimbursed).
"Jan 2025" (monthly expenses sheet) → Logs expenses with the following columns:
- B: Date
- C: Amount
- D: Category
- E: Spent By (Dropdown: Anlo, Vira, Ken)
- F: Description

Each person pays for some expenses throughout the month, but all expenses are meant to be shared equally among the three members.

For each person in the "Expenses Summary" sheet, I need formulas to calculate:

"To Pay" → How much a person owes because they benefited from shared expenses paid by others.
- A person should contribute 1/3 of each expense that they did not pay themselves.
"To Receive" → How much a person should be reimbursed because they paid more than their share.
- This is the total amount they paid minus their own fair share (1/3 of every expense).

Date	Amount (C)	Category	Spent By (E)	Description
Jan 3	3,790.50	Food	Vira	Groceries
Jan 3	2,369.00	Food	Vira	More groceries
Jan 3	315.00	Utilities	Ken	Gas bomb
Jan 20	555.00	Food	Vira	Shopping
Jan 20	106.00	Food	Ken	Pet food
Jan 24	75.00	Food	Ken	Milk powder
Jan 24	50.00	Food	Ken	Soup meat
Jan 24	137.50	Food	Vira	Larger milk pack
Jan 24	100.00	Food	Vira	Smoked sausage
Jan 27	4,959.50	Food	Vira	Grocery store
Jan 27	937.50	Food	Vira	Supermarket
Jan 27	406.88	Electricity	Vira
Jan 27	94.50	Water	Vira	SWM
Jan 27	673.46	Internet	Vira	Telesur

For "To Pay":

=SUMPRODUCT(
   (INDIRECT(TEXT($B$4,"mmm yyyy")&"!C3:C1000") / 3) * (INDIRECT(TEXT($B$4,"mmm yyyy")&"!E3:E1000") <> A14)
)

For "To Receive":

=SUMIF(
   INDIRECT(TEXT($B$4,"mmm yyyy")&"!E3:E1000"), A14, 
   INDIRECT(TEXT($B$4,"mmm yyyy")&"!C3:C1000")
) - B14

Both formulas return errors or incorrect values.

A working formula for "To Pay" that correctly sums 1/3 of every expense they did not pay.
A working formula for "To Receive" that calculates the amount they overpaid and should get reimbursed.

Any help is greatly appreciated!