Best Of

<> is the expression for "not equal to", so <>"" is read as "not equal to BLANK". If it does not matter what is in the cell, that is to say "any value", then the way to represent that is <>"".The alternative is something specific or one of many possible values.

You can also use the combination of ISBLANK() and NOT() to evaluate if "any value" is in the cell. For example, ISBLANK([Column 2]@row) will check to see if the cell is blank. Enclosing that statement within NOT(), e.g, NOT(ISBLANK([Column 2]@row)), essentially means "[Column 2]@row is not blank".

You can nest functions within other functions so long as you abide by the function's syntax (structure).

I'm curious if a solution has been created / discovered for this problem as well @Julio S.

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)