# 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. • 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?

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.