I need a formula for a budget calendar

[Ssparks258]

I'm looking for some help trying to figure out how to write this formula.

In column J, I want to display a number based on a few conditions:

1. If it is the first of the month, I want to add the rollover amount (cell C38)
2. If it is a Friday, I want add the weekly amount (cell C37)
3. I want to carry down the amount from the cell above in the equation. (Not sure how to make this work with cell J27 being text that I don't want included)
4. I want to deduct any amount from the first table (A27-C34) if the day matches the date from column I.

I can't figure out how to nest these functions within each other to achieve the result I'm looking for.

If anyone has any out how to nest these functions within each can't other suggestions please help. I'm also open to easier ways of achieving the same outcome. Thank you and I hope to hear from someone soon.

 

[levipe]

See if this works

=IF(DAY($I28)=1,$C$38,SUM($J27,FILTER($C$28:$C$34,$A$28:$A$34=DAY($I28),0),IF(WEEKDAY($I28,2)=5,$C$37,0)))

 

[Tetra201]

Another approach:

=SUM(J27,$C$38*(DAY(I28)=1)+$C$37*(WEEKDAY(I28)=6)-SUMIFS($C$28:$C$34,$A$28:$A$34,DAY(I28)))

 

[Ssparks258]

See if this works

=IF(DAY($I28)=1,$C$38,SUM($J27,FILTER($C$28:$C$34,$A$28:$A$34=DAY($I28),0),IF(WEEKDAY($I28,2)=5,$C$37,0)))

I appreciate the time you took with this formula. I tried to apply it today but it seems to be missing the step to deduct any amount if the day has a bill due on it. Thank you so much though.

 

[Ssparks258]

Another approach:

=SUM(J27,$C$38*(DAY(I28)=1)+$C$37*(WEEKDAY(I28)=6)-SUMIFS($C$28:$C$34,$A$28:$A$34,DAY(I28)))

This seems to be working perfectly. Thank you so much for taking your time to write this for me. I tried so many different ways and couldn't get it to work. Thank you again.