calculate cases for each for multi year metrics sheet
Hello, I combined the separate sheets i had for each which calculate the # of cases into a single one because it seems to easier to work with. For each individual condition, I calculate the # or cases per month in any particular year, however for the total # of cases for year the formula seems to calculate the total for all Jan, all Feb etc across all years. I need it to calculate for one particular month. Thank you for your help!
The numbers you see is for all the years not only 2021, and in UPARSEABLE I tried to add Year criteria but it's not working.
for each conditions it works:
=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1), {Year}, "2021")
I need to add a year and a month to the total number of cases by month:
=COUNTIFS({Event date}, @cell = $[Numerical Value]@row, {Year}, HAS(@cell, [2021]$1)
or I tried this one too:
=COUNTIFS({Event date}, @cell = $[Numerical Value]@row, {Year}, “2021”)
I have the references defined correctly. Thank you!
Best Answer

@Genevieve P. this one works!
=COUNTIFS({Event date}, IFERROR(MONTH(@cell), 0) = [Numerical Value]@row, {Event date}, IFERROR(YEAR(@cell), 0) = 2018)
thank you so much!
Answers

Hi @OshaK
Try the following:
=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1), {Year}, 2021)
Without quotes around the year. Either that, or it looks like you have the "Year" in the cell to the left, so you could go:
=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1), {Year}, Year@row)
This is presuming that your source sheet has a column "Month" that only lists the number of the month down the column, and a column "Year" that only has the numerical year listed down the column (versus a date cell). Is that correct?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. thank you for your help! Sorry, I didn't explain what i need clearly. I got the Complications all working, but I need to be able to count total cases by month for each year separately.
I currently have a formula without a Year, but it calculates ALL cases for ALL Marches, ALL Aprils etc., whereas i need them to be separated for each month of each year.
This sums the months the the same names together:
=COUNTIF({Event date}, IFERROR(MONTH(@cell), 0) = [Numerical Value]@row)
and when I tried to add a Year:
=COUNTIFS([Reoperation (unplanned)]28{Event date}, @cell = $[Numerical Value]@row, {Year}, “2021”)
it gives me =UNPARSEABLE error.
A source sheet has both Month and Year columns, yes.
Each year currently has 12 months listed in the Months column (with a different year next to the month name), and the Numerical Value column repeats values 112 for each year  could this be a problem?
Thank you!

Hi @OshaK
Your set up is not a problem 🙂
However you don't need to use quotes around finding the Year value, if it's like in your screen capture (single numbers in a year column).
So instead of
{Year}, “2021”
you'd want
{Year}, 2021
However based on your description right now it sounds like {Event date} is a date column that you're looking at for the month and year, versus two individual "month" and "year" columns. Is that correct?
If so, try:
=COUNTIFS({Event date}, IFERROR(MONTH(@cell), 0) = [Numerical Value]@row, {Event date}, IFERROR(YEAR(@cell), 0) = 2021)
You can swap out the 2021 at the end to be a cell reference, if that's preferred:
=COUNTIFS({Event date}, IFERROR(MONTH(@cell), 0) = [Numerical Value]@row, {Event date}, IFERROR(YEAR(@cell), 0) = Year@row)
Try that last formula and let me know if it works!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. this one works!
=COUNTIFS({Event date}, IFERROR(MONTH(@cell), 0) = [Numerical Value]@row, {Event date}, IFERROR(YEAR(@cell), 0) = 2018)
thank you so much!

Glad to hear it! 🙂 No problem
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. Hello, you helped me a lot with the formulas back in Feb 2023, thank you so much! the sheets have been workin beautifully, but i need to expand it and ran into a formula problem.
I now need to calculate the complications by month/year but not for all faculty but for each one individually. We have a multi choice drop down column "Complications" and now I have to add the names from the second multi choice drop down 'Faculty Names' column. I've added it into the existing formula but it returns 0 only, no realy numbers and no errors. I can't seem to find an error, could you please take a lot at the formula if the syntax is correct? Thank you!
they all refer to another single sheet.
=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, "Surgical Complications"), {Attendings Involved}, HAS(@cell, "Faculty Name"), {Year}, "2023")

Hi @OshaK
The syntax looks correct to me! 🙂
How is your {Year} column formatted? If it's showing numbers (values appearing on the right side of the cell), then you may want to try looking for 2023 in your formula instead of "2023":
=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, "Surgical Complications"), {Attendings Involved}, HAS(@cell, "Faculty Name"), {Year}, 2023)
The quotes turn your numbers into text, so it may not be finding a match.
Let me know if that was the issue!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi @Genevieve P. thank you so much for your help. I think i figured it out over the weekend, and the year is working with the quotation marks. Thank yuo!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!