Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.
I am currently trying to compare data from one month ( in sequential order, January to December 2024) example: comparing the difference in orders for February as opposed to January. All of this data is linked from one sheet to a master sheet where I have the data separated by region and can sum the dat for the corresponding month. My main issue is telling Smartsheet to go to that month ( January, find the order numbers and subtract it from February's), I would like this to be automated completely so when I look at my dashboard its updated to the current month and I no longer have to do this process manually.
The formulas will all automatically update to the current month and the last month.
To put these formula on a different sheet from the one with the data in, you can use cross sheet references. You would create the result sheet with a list of regions on your second sheet, just like the image in step 7.
Then instead of referencing the columns, you would set up a cross sheet reference for each. The resulting formula would look like:
Orders this month, this year:
=SUMIFS({Data sheet orders column}, {Data sheet regions column}, [Region to find]@row, {Data sheet date column}, MONTH(@cell) = MONTH(TODAY()), Date:Date, YEAR(@cell) = YEAR(TODAY()))
Orders last month:
=SUMIFS({Data sheet orders column}, {Data sheet regions column}, [Region to find]@row, {Data sheet date column}, MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), {Data sheet date column}, YEAR(@cell) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))
You are basically telling the formula to look in the other sheet, in the columns you define, rather than look in the current sheet using the column names. Your names will differ from mine and you need to set up the cross sheet references in your sheet, you cannot just paste in the references.
If you are not familiar with cross sheet references, have a look at this:
Or this discussion, where I talk a user through setting up a cross sheet reference for a SUMIFS formula:
Let me know if you have any problems.
Hi @jec_5274
There are a couple of functions that are going to help you. The TODAY function will tell you the current date and the MONTH function will give you the month from a date (whether that date is a date in a cell or TODAY). You might also want to use the YEAR function so you can compare January with the previous December, and so that if you have multiple years within a sheet you aren't comparing all of the Februarys with all of the Januarys.
Then you use SUMIFS to do your calculations with your criteria being region, and month, and ideally year as well.
I am going to assume your data looks something like this:
And you are using a formula like this:
=SUMIF(Region:Region, [Region to find]@row, Orders:Orders)
To create a summary table like this:
You can do this on another sheet using cross-sheet references but I am doing it within the sheet so you can see what I am doing. My SUMIF is checking the Region column to see if it matches my Region to find, and where it does summing the values in the Orders column.
I think you are fine here. So, now onto your question. I will build your formula step by step so you can see what it is doing and adapt as necessary.
We use the MONTH function and the TODAY function.
=MONTH(TODAY())
Right now, on Feb 10th, this will return 2.
Similarly, we can use
=YEAR(TODAY())
We can take the SUMIF that already checks for the region and convert this to a SUMIFS to also look at the date
This is the original
=SUMIF(Region:Region, [Region to find]@row, Orders:Orders)
And we add the part in bold to it
=SUMIFS(Orders:Orders, Region:Region, [Region to find]@row, Date:Date, MONTH(@cell) = MONTH(TODAY()))
The syntax of SUMIFS is different from SUMIF in that the column to sum goes first, then we have the Region column must match the region in the current row, and then the Date column is evaluated and the month of that date must match the month of today's date.
This gives us this:
The formula in step 3 is only checking for February, so you'll notice Emerald City's total orders include those in February 2023. This might not be an issue for you, but in case it is, we simply add another criterion to our SUMIFS. This time to check the year in the Date column matches the current year. This looks like this:
=SUMIFS(Orders:Orders, Region:Region, [Region to find]@row, Date:Date, MONTH(@cell) = MONTH(TODAY()), Date:Date, YEAR(@cell) = YEAR(TODAY()))
So if today is February the month for last month is 1. We can find this using
=MONTH(TODAY()) - 1
However, if this was January, then the formula would return 0 as month number 1 minus 1 is 0 (not 12).
So we need to add a little IF into the formula.
=IF(MONTH(TODAY())=1,12,MONTH(TODAY()) - 1)
This says that if it is month number 1, return month 12. If it is not currently month 1, then return whatever this month's number is, minus 1.
For 11 months of the year, the year last month will be the same as this month, but if it is January you are going to need to look at the previous year. So again we can use an IF.
=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY()))
This says that if it is month number 1, then return the current year minus 1 (as the previous month was in the previous year). If it is not currently month 1, then return the current year.
We can use the SUMIFS from step 4 and adjust it slightly to use the formula we made in steps 5 and 6.
This is what we had:
=SUMIFS(Orders:Orders, Region:Region, [Region to find]@row, Date:Date, MONTH(@cell) = MONTH(TODAY()), Date:Date, YEAR(@cell) = YEAR(TODAY()))
And we are going to change the parts in bold to be last month not this month.
=SUMIFS(Orders:Orders, Region:Region, [Region to find]@row, Date:Date, MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), Date:Date, YEAR(@cell) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))
This gives you this column on the right here
Now you can do any math that you want either using the columns we made in step 3 or 4 and 7 or using those formulas in a new column.
Thank you for your help! This is definitely what I was looking for, but is there anyway to make this process automatic ( I am basically using the data you have shown me) where the data can be inputted on one sheet ( ex: orders in random dates) and then is linked to another sheet (ex: order dashboard) where the link automatically updates the current month information ( taking the step out of updating the dashboard).
The formulas will all automatically update to the current month and the last month.
To put these formula on a different sheet from the one with the data in, you can use cross sheet references. You would create the result sheet with a list of regions on your second sheet, just like the image in step 7.
Then instead of referencing the columns, you would set up a cross sheet reference for each. The resulting formula would look like:
Orders this month, this year:
=SUMIFS({Data sheet orders column}, {Data sheet regions column}, [Region to find]@row, {Data sheet date column}, MONTH(@cell) = MONTH(TODAY()), Date:Date, YEAR(@cell) = YEAR(TODAY()))
Orders last month:
=SUMIFS({Data sheet orders column}, {Data sheet regions column}, [Region to find]@row, {Data sheet date column}, MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), {Data sheet date column}, YEAR(@cell) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))
You are basically telling the formula to look in the other sheet, in the columns you define, rather than look in the current sheet using the column names. Your names will differ from mine and you need to set up the cross sheet references in your sheet, you cannot just paste in the references.
If you are not familiar with cross sheet references, have a look at this:
Or this discussion, where I talk a user through setting up a cross sheet reference for a SUMIFS formula:
Let me know if you have any problems.
Thank you I will try this out!
