calculate cases for each for multi year metrics sheet

OshaK

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!

OshaK

@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!

Genevieve P.

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

OshaK

@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 1-12 for each year - could this be a problem?

Thank you!

Genevieve P.

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

OshaK

@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!

Genevieve P.

Glad to hear it! 🙂 No problem

OshaK

@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")

Genevieve P.

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

OshaK

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!