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({E-mail 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 cross-sheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
The INVALID REFERENCE is referring to your cross-sheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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 cross-sheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 cross-sheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!