Need some help with formula

[mike4slund]

Hello, I have searched and can not seem to locate the help I need.

I have a spreadsheet that is tracking when a person took a class, there are 8 options. Each class is worth a dollar amount. If they are terminated within 180 days of taking this class, thy are to be charged accordingly. The formula I believe should be something like this, I am just struggling to get that into something excel understands. I have also attached a screenshot.

=IF S5 (termed date) is 180 days less than any date in K5:R5 then add the total of that column dollar amount together.

 

[JamesCanale]

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		$ 54.00	$ 15.00	$ 87.50	$ 97.50	$ 15.00	$ 65.00	$ 60.00	$ 40.00
2	Employee	Training1	Training2	Training3	Training4	Training5	Training6	Training7	Training8	Termed Date	Owed
3	Fred	12/2/2024			1/14/2024		11/27/2024			12/4/2024	$ 119.00
4	Harry	12/5/2024				10/5/2024
5	Sally		1/1/2024		10/1/2024		1/1/2023		12/1/2024	1/1/2025	$ 137.50
Sheet25

Cell Formulas
Range	Formula
K3:K5	K3	=IF(J3<>"",SUM((B3:I3>=J3-180)*$B$1:$I$1),"")

 

[mike4slund]

Wow! Thank you for the assistance! I hope I can get the rest of the way there. If i am calculating correctly, It looks like something is not adding right in your formula. Based on dates. K3 should equal $216.50 not $119 and k5 should equal $152.50.

 

[mike4slund]

sorry for the double post. but now that i kept the formula and just re-entered the data, it appears to be calculating correctly.

 

[mike4slund]

actually, i think the error is when it comes close to december dates for some reason.

 

[mike4slund]

last reply! It is working!! thank you so much!!!!!!!!!