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

@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

@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

@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

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
Check out the Formula Handbook template!