Help with month end formula
Hi,
I'm building a dashboard to track referral responses. The missing part of my dashboard is I need create a formula that gives the total for each category by month and year.
I can't figure out what function I need to add the the formulas I already have to break down the total by month and year.
The formulas I've built so far:
Only counts the referral type "Emergency  24 hours IHCA response needed
=COUNTIF({Referral Type}, ="Emergency  24 hour IHCA response needed")
Counts the total number or referrals received:
=COUNT({Timestamp})
Gives a total of successful referrals:
=COUNTIF({Email Outcome}, "Successful")
Thank you
Frances
Best Answers

You're on the right track! The next step is to use a COUNTIFS (plural) function to look for multiple criteria. Then you can use the MONTH function and the YEAR function to search for certain dates.
With COUNTIFS, first you list the range (I'll call this your {Date column}), then you list your criteria afterwards. Since it's a crosssheet formula, you'll want to have @cell as the "date" within the MONTH and YEAR functions, to specify it should look in the individual cells of the previously listed column.
Ex, this would look for January (or month 1) in the year 2020:
=COUNTIFS({Referral Type}, ="Emergency  24 hour IHCA response needed", {Date column}, MONTH(@cell) = 1, {Date column}, YEAR(@cell) = 2020)
Now, you can sometimes receive errors with date functions when it's looking through entire columns like this and coming across blank cells. To prevent that, you can wrap an IFERROR function around both the MONTH and the YEAR functions.
Full Formula:
=COUNTIFS({Referral Type}, ="Emergency  24 hour IHCA response needed", {Date column}, IFERROR(MONTH(@cell), 0) = 1, {Date column}, IFERROR(YEAR(@cell), 0) = 2020)
You can use that same statement in each of your formulas, just change out the criteria. So for February, have the MONTH bit search for 2 instead of 1:
IFERROR(MONTH(@cell), 0) = 2
For 2019, have the YEAR search for 2019:
IFERROR(YEAR(@cell), 0) = 2019
Let me know if this makes sense or if you have any questions!
Cheers,
Genevieve

The INVALID REFERENCE is referring to your crosssheet references, the ones {in these}.
I had placed example text in my references to show what columns you should be selecting in the other sheet, but you will need to go through the "reference another sheet" process in order to link each of these with the correct column.
For example, the {Date column} reference would be the column in your source sheet that has the dates you're searching through. Copy/Paste the formula, then delete out the reference in the formula and select "reference another sheet":
Then when you find your source sheet, select the date column and rename the reference something easily identifiable... perhaps even "Date column" so you don't need to do the same thing for the second reference.
Does that make sense? You can learn more about cross sheet references here.
Cheers!
Genevieve
Answers

You're on the right track! The next step is to use a COUNTIFS (plural) function to look for multiple criteria. Then you can use the MONTH function and the YEAR function to search for certain dates.
With COUNTIFS, first you list the range (I'll call this your {Date column}), then you list your criteria afterwards. Since it's a crosssheet formula, you'll want to have @cell as the "date" within the MONTH and YEAR functions, to specify it should look in the individual cells of the previously listed column.
Ex, this would look for January (or month 1) in the year 2020:
=COUNTIFS({Referral Type}, ="Emergency  24 hour IHCA response needed", {Date column}, MONTH(@cell) = 1, {Date column}, YEAR(@cell) = 2020)
Now, you can sometimes receive errors with date functions when it's looking through entire columns like this and coming across blank cells. To prevent that, you can wrap an IFERROR function around both the MONTH and the YEAR functions.
Full Formula:
=COUNTIFS({Referral Type}, ="Emergency  24 hour IHCA response needed", {Date column}, IFERROR(MONTH(@cell), 0) = 1, {Date column}, IFERROR(YEAR(@cell), 0) = 2020)
You can use that same statement in each of your formulas, just change out the criteria. So for February, have the MONTH bit search for 2 instead of 1:
IFERROR(MONTH(@cell), 0) = 2
For 2019, have the YEAR search for 2019:
IFERROR(YEAR(@cell), 0) = 2019
Let me know if this makes sense or if you have any questions!
Cheers,
Genevieve

Hi @Genevieve P
Thanks for your help. When I input the formula it returns #INVALID REF error. I'm not sure what I am missing for this formula. I think it has to do with the parentheses. I tired to let Smartsheet auto input the parentheses, I still get the same error.

Frances

The INVALID REFERENCE is referring to your crosssheet references, the ones {in these}.
I had placed example text in my references to show what columns you should be selecting in the other sheet, but you will need to go through the "reference another sheet" process in order to link each of these with the correct column.
For example, the {Date column} reference would be the column in your source sheet that has the dates you're searching through. Copy/Paste the formula, then delete out the reference in the formula and select "reference another sheet":
Then when you find your source sheet, select the date column and rename the reference something easily identifiable... perhaps even "Date column" so you don't need to do the same thing for the second reference.
Does that make sense? You can learn more about cross sheet references here.
Cheers!
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!