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!

Screenshot 2023-02-10 at 11.40.54 AM.png

Find more posts tagged with

Accepted answers

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!

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.