Annual Recurring Revenue (ARR) calculation between last year and this year.

Joseph Aloysias

Hi,

I would like to calculate the Annual Recurring Revenue (ARR) between last year and this year to identify any new additional sales to the existing customer list.

Using the "Company Name", I want to calculate the difference in the "Total Revenue", "Close Date" column.

Please help me with the formula.

Thanks

Brian_Richardson

I'm assuming you have a sheet that has three columns at least, one for Company Name, on for Total Revenue, and one for Close Date? And you have potentially the same company name showing up multiple times on the sheet, right?

If so then add a Text/Number column for Revenue Delta and use this formula using SUMIFS:

=SUMIFS([Total Revenue]:[Total Revenue], [Company Name]:[Company Name], [Company Name]@row, [Close Date]:[Close Date], YEAR(@cell) = YEAR(TODAY())) - SUMIFS([Total Revenue]:[Total Revenue], [Company Name]:[Company Name], [Company Name]@row, [Close Date]:[Close Date], YEAR(@cell) = YEAR(TODAY()) - 1)

Once you copy that formula into a cell and hit enter, right click the cell and choose Convert to Column Formula. That will apply it to every row in the sheet at all times, regardless of what's moved or changed with the rows.

Note that this gives you the delta between the totals for 2024 and totals for 2023 for each company name. If the company name repeats then it will show you the same amount on each repeated company name line. So if you're then reporting on these numbers, you'll want to Average them or otherwise filter to have only 1 row contributing to the final report.

Zoomed up:

For example on this sheet, the first row is Company A. The formula looks through the entire sheet, finds all amounts for Company A where the Close Date year is the current year. That total is $500.

Then it looks for the same Company A where the Close Date is last year and totals that amount. That total is $200.

Then it subtracts the two to arrive at $300. Which you see on both Company A lines.

Explanation:

This formula is two SUMIFS statements.

• The first SUMIFS finds the total revenue for all rows where the Company Name matches the name on the row, and the year of the Close Date is the same year as the year for TODAY().
• The second SUMIFS finds total revenue for all rows where the Company Name matches the name on the row, and the year of the Close Date is the same year as the year for TODAY(), minus 1 (the previous year). Then it subtracts this amount from the first SUMIFS.
• You'll see @cell is used in the formula, this is a Smartsheet function that you can use when you have one function (like YEAR) embedded in another function (like COUNTIFS) that is iterating over rows. It basically means… "for each row that you look at, check that the YEAR of the cell you're on is the same as the YEAR for today. Then go on to the next row and check the same thing".
• @cell is different than @row, which you also see. @row just means "the value of the named column on the row that I'm on". So for row 1, the formula says "look at Company Name on my current row (row 1), and get the value ("Company A")

Adjusting to work for all years

Right now the way you asked for it is to have current year minus last year. But if you have more years than that on the sheet, you could adjust this to show "Delta of Close Date year and prior year". That formula would be

=SUMIFS([Total Revenue]:[Total Revenue], [Company Name]:[Company Name], [Company Name]@row, [Close Date]:[Close Date], YEAR(@cell) = YEAR([Close Date]@row)) - SUMIFS([Total Revenue]:[Total Revenue], [Company Name]:[Company Name], [Company Name]@row, [Close Date]:[Close Date], YEAR(@cell) = YEAR([Close Date]@row) - 1)