IF to subtract multiple rows based on other cells contents

[Kurtin]

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!!

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

 

[Eric W]

Try:

Book1
	D	E	F	G	H
1	TOTAL $ AMOUNT		100	$ AMOUNT OWED REMAINING	80
2	QTY	DATE OF PURCHASE	$ AMOUNT	PAID (YES/NO)	DATE PAID
3			20	yes
4			20
5			20
6			20
7			20
Sheet5

Cell Formulas
Range	Formula
F1	F1	=SUM(Table1[$ AMOUNT])
H1	H1	=F1-SUMIF(Table1[PAID (YES/NO)],"yes",Table1[$ AMOUNT])

 

[Kurtin]

Try:

Book1
	D	E	F	G	H
1	TOTAL $ AMOUNT		100	$ AMOUNT OWED REMAINING	80
2	QTY	DATE OF PURCHASE	$ AMOUNT	PAID (YES/NO)	DATE PAID
3			20	yes
4			20
5			20
6			20
7			20
Sheet5

Cell Formulas
Range	Formula
F1	F1	=SUM(Table1[$ AMOUNT])
H1	H1	=F1-SUMIF(Table1[PAID (YES/NO)],"yes",Table1[$ AMOUNT])

Worked PERFECTLY thank you so much! I understand where I went wrong

 

[Eric W]

Happy to help! Thanks for the feedback.