SUM including monthly amounts and annual divided by 12?

[charlesstricklin]

I have a weird question: I have a budget spreadsheet that has three columns one Payee, another the day of the month paid, and then the amount. This is fine for services paid monthly, but may I also include annual and semi-annual services? For example, I have a phone plan that is paid $480/year in August 25th, equating to $40/month and an insurance plan that is paid every six months totalling $952.71 beginning January 1, 2025. I pay varying amounts between $160.86 and $158.36, but for simplicity let's just say that I pay $925.71/6 = $160.45.

Is there a way I may enter those non-monthly plans in such a way that they are included in a monthly sum? See below for an example:

Payee​	Day​	Amount​
Rent	1​	$1,000​
Electricity	20​	$100​
Phone	August 25, 2025​	$40​
Insurance	July 1st, 2025​	$160.45​
Total		$1,300.45​

I'm sure what I'm asking, if even possible, will require additional columns/rows and would be formatted differently, but if someone could help me with this quandary I'd appreciate it. In the meantime, for those items, I'm leaving the day cell blank and entering the monthly amount.

 

[charlesstricklin]

I've been considering this and maybe I ought to change the Day column to period or just add an additional column named Frequency and choose between Monthly, Biannually, and Annually?

 

[RobP]

Hi, Maybe something like this can help you, where you put your data either into C or D, and have a "Monthly Equiv" Column added ?

Rob

Book1
	A	B	C	D	E	F
1	Payee	Payment Day	Month	Year	Monthly Equiv
2	Rent	1	1000		1000
3	Electricity	20	100		100
4	Phone	August 25, 2025		480	40
5	Insurance	July 1st, 2025	160.45		160.45
6	Total				1300.45
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(OR(D2="",D2=0),C2,D2/12)
E6	E6	=SUM(E2:E5)

 

[charlesstricklin]

Hi, Maybe something like this can help you, where you put your data either into C or D, and have a "Monthly Equiv" Column added ?

Rob

Book1
	A	B	C	D	E	F
1	Payee	Payment Day	Month	Year	Monthly Equiv
2	Rent	1	1000		1000
3	Electricity	20	100		100
4	Phone	August 25, 2025		480	40
5	Insurance	July 1st, 2025	160.45		160.45
6	Total				1300.45
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IF(OR(D2="",D2=0),C2,D2/12)
E6	E6	=SUM(E2:E5)

Yeah, that could work. Nice and simple. Thanks.

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works.
I have done that for you this time.

 

[charlesstricklin]

In future please mark the post that contains the solution, rather than your post saying it works.
I have done that for you this time.

I did that the second I posted my reply. We must've done it at the same time or something.