Heya,
So I have a total amount, which is the sum of a column, but I now want to have a cell for leftover amount. I am wondering how to subtract the amounts from the total based on if another cell says yes or not.
I attempted a formula but that just resulted in a #SPILL! error, which spans the same amount of cells that is listed in the $amount column. If anyone has any other formulas that could work, or a way to fix this one it would be greatly appreciated. Thank you in advance!!
So I have a total amount, which is the sum of a column, but I now want to have a cell for leftover amount. I am wondering how to subtract the amounts from the total based on if another cell says yes or not.
I attempted a formula but that just resulted in a #SPILL! error, which spans the same amount of cells that is listed in the $amount column. If anyone has any other formulas that could work, or a way to fix this one it would be greatly appreciated. Thank you in advance!!
RevHappy Expenses Tracker.xlsx | |||||||
---|---|---|---|---|---|---|---|
D | E | F | G | H | |||
1 | TOTAL $ AMOUNT | $ 100.00 | $ AMOUNT OWED REMAINING | #SPILL! | |||
2 | QTY | DATE OF PURCHASE | $ AMOUNT | PAID (YES/NO) | DATE PAID | ||
3 | $ 20.00 | yes | |||||
4 | $ 20.00 | ||||||
5 | $ 20.00 | ||||||
6 | $ 20.00 | ||||||
7 | $ 20.00 | ||||||
EXPENSES |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F1 | F1 | =SUM(Table1[$ AMOUNT]) |
H1 | H1 | =IF(Table1[PAID (YES/NO)]="YES",F1-Table1[$ AMOUNT],"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A3:H7 | Expression | =$G3="" | text | NO |
A3:H7 | Expression | =$G3="YES" | text | NO |