What functions can I use to track subscriptions?

Posted on

QUESTION :

I am struggling to make a spreadsheet which will help me take control of member’s subscription fees. Fees are paid per month.

Example spreadsheet

  • The spreadsheet has 12 horizontal cells, each one for a different month of the year. I created a 13th one in which I want to import a function that calculates the total amount of money the member owes based on current month.
  • I would also like to have each cell of the month that is not paid, to be colored red.

How can I accomplish this?

ANSWER :

The amount that a person owes for the current year, based on the current month, is

=MONTH(TODAY()) * monthly_dues

This assumes that the member has been a member all year and that his account
was even at the end of the previous year. 
To get a formula that works in other years (stored in A3), use

=IF(A3<YEAR(TODAY()), 12, IF(A2>YEAR(TODAY()), 0, MONTH(TODAY()))) * monthly_dues

This gets trickier if the dues vary over time.

I would also like to have each cell of the month that is not paid, to be colored red.

use conditional formatting to do this.

The spreadsheet has 12 horizontal cells, each one for a different month of the year. I created a 13th one in which I want to import a function that calculates the total amount of money the member owes based on current month.

I don’t understand this one well, does the picture showing only one person’s paying or the amount of people that subscribes. you can try use column() function to get the column of a cell to calculate the month of that cell.

Leave a Reply

Your email address will not be published.