If/then formula to show balance due
Hi
I'm looking to create a formula calculating balance due. We have 12 possible payments to deduct from the total, and need to see the balance at any given time. The way we have the sheet set up is a "Total Gross" column and then for Payments 112 there is a column for "Payment 1 Amount" which is what is expected, and then a column for "Payment 1 Received" however that column records a date, not the amount of the payment. Then repeated for Payments 212. I am looking to create a column that would only deduct the "Payment Amount" if a date has been entered in "Payment Received"
I realize the easy fix would be to add another column for the actual amount received and just deduct those, however this sheet has approx 5k rows of data and unfortunately would be way too much to go back in and input potentially 60k payments. Therefore, configuring this formula is the route we have decided to go. I have tried numerous times, but keep getting errors and was hoping someone with more formula experience could help me sort it out
Thank you!
Best Answer

If I understand you correctly, this should do it. This assumes the dates in your Payment X Received columns are correctly formatted as dates.
=[Total Gross]@row  IF(ISDATE([Payment 1 Received]@row), [Payment 1 Amount]@row, 0)  IF(ISDATE([Payment 2 Received]@row), [Payment 2 Amount]@row, 0)  IF(ISDATE([Payment 3 Received]@row), [Payment 3 Amount]@row, 0)  IF(ISDATE([Payment 4 Received]@row), [Payment 4 Amount]@row, 0)  IF(ISDATE([Payment 5 Received]@row), [Payment 5 Amount]@row, 0)  IF(ISDATE([Payment 6 Received]@row), [Payment 6 Amount]@row, 0)  IF(ISDATE([Payment 7 Received]@row), [Payment 7 Amount]@row, 0)  IF(ISDATE([Payment 8 Received]@row), [Payment 8 Amount]@row, 0)  IF(ISDATE([Payment 9 Received]@row), [Payment 9 Amount]@row, 0)  IF(ISDATE([Payment 10 Received]@row), [Payment 10 Amount]@row, 0)  IF(ISDATE([Payment 11 Received]@row), [Payment 11 Amount]@row, 0)  IF(ISDATE([Payment 12 Received]@row), [Payment 12 Amount]@row, 0)
Answers

If I understand you correctly, this should do it. This assumes the dates in your Payment X Received columns are correctly formatted as dates.
=[Total Gross]@row  IF(ISDATE([Payment 1 Received]@row), [Payment 1 Amount]@row, 0)  IF(ISDATE([Payment 2 Received]@row), [Payment 2 Amount]@row, 0)  IF(ISDATE([Payment 3 Received]@row), [Payment 3 Amount]@row, 0)  IF(ISDATE([Payment 4 Received]@row), [Payment 4 Amount]@row, 0)  IF(ISDATE([Payment 5 Received]@row), [Payment 5 Amount]@row, 0)  IF(ISDATE([Payment 6 Received]@row), [Payment 6 Amount]@row, 0)  IF(ISDATE([Payment 7 Received]@row), [Payment 7 Amount]@row, 0)  IF(ISDATE([Payment 8 Received]@row), [Payment 8 Amount]@row, 0)  IF(ISDATE([Payment 9 Received]@row), [Payment 9 Amount]@row, 0)  IF(ISDATE([Payment 10 Received]@row), [Payment 10 Amount]@row, 0)  IF(ISDATE([Payment 11 Received]@row), [Payment 11 Amount]@row, 0)  IF(ISDATE([Payment 12 Received]@row), [Payment 12 Amount]@row, 0)

Thank you very much! This seems to be working!