How do i create a formula to source information from another sheet with multiple countifs?

Options
Webby
Webby
edited 01/25/23 in Formulas and Functions

I am trying to setup an automated metrics sheet from many other sheets and I am stuck on the very first "reference another sheet" formula due to the conditions (countifs) that I'm trying to get it to pick up. Here's the example:

The metric sheet is trying to count KPI's from other sheets, for this particular instance a hazard count. I have another sheet where information is gathered called "Hazard Report Register" 2 of the columns in the sheet will effect the countifs they are; "Date" and "Person Raising Hazard"

What I ultimately want it to do is; in a cell tell me how many times a particular individual has raised a hazard in that month, but I believe the "Date" part is messing with my formula.

(Note the "Hazard Report Register" has hazards entered from other years then the current e.g. January 2022 that I don't want it to count).

The below is a screenshot of a section of the metrics sheet,

John Smiths Jan 2023 tally should be at 2.

Any help on the required Formula would be appreciated, I will then have to duplicate it for all my other sheets and KPIs.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Webby

    Try this. You will have to change the Month value for each column. Almost always when I use a date range I add either an IFERROR or an ISDATE() to try to mitigate errors thrown by non-date cells when Date functions are used. You will need to change the Month number for each column. This is for the January column.

    =COUNTIFS({Hazard Report Register Date column}, IFERROR(MONTH(@cell),0)=1, {Hazard Report Register Date column}, IFERROR(YEAR(@cell), 0)=2023, {Hazard Report Register Person Raising Hazard column}, Name@row)

    You must insert the cross sheet references in your sheet - you cannot simply copy paste this formula into your sheet. If you are unfamiliar with cross sheet references see the link at bottom.

    Will this work for you

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Webby

    Try this. You will have to change the Month value for each column. Almost always when I use a date range I add either an IFERROR or an ISDATE() to try to mitigate errors thrown by non-date cells when Date functions are used. You will need to change the Month number for each column. This is for the January column.

    =COUNTIFS({Hazard Report Register Date column}, IFERROR(MONTH(@cell),0)=1, {Hazard Report Register Date column}, IFERROR(YEAR(@cell), 0)=2023, {Hazard Report Register Person Raising Hazard column}, Name@row)

    You must insert the cross sheet references in your sheet - you cannot simply copy paste this formula into your sheet. If you are unfamiliar with cross sheet references see the link at bottom.

    Will this work for you

    Kelly

  • Webby
    Options

    Thank you very much Kelly.

    Just like you advised, this is how it ended up

    =COUNTIFS({Hazard Date}, IFERROR(MONTH(@cell), 0) = 1, {Hazard Date}, IFERROR(YEAR(@cell), 0) = 2023, {Hazards by person}, [01. Name]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!