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

Webby

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.

Kelly Moore

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

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Kelly Moore

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

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Webby

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)