COUNTIFS Function

Jessica Howell

Hello everyone,

Need some help with this formula, thinking i have too much going on here?

=COUNTIFS({Environmental Incident Tracking Range 1}, AND({Environmental Incident Tracking Range 3} = 1, YEAR({Environmental Incident Tracking Range 4} = 2020, {Environmental Incident Tracking Range 1}, "Leak")))

Environmental Incident Tracking is the sheet I am trying to pull from.

Basically, for every quarter, I am looking for the total number of leaks reported, for the year 2020

TIA!

Paul Newcome

Try something like this instead...

=COUNTIFS({Environmental Incident Tracking Range 1}, "Leak", {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)

If that does not work are you able to provide a screenshot of the source data with sensitive/confidential data removed, blocked, and/or replaced with "dummy/mock" data?

Jessica Howell

@Paul Newcome Came up unparseable

I have attached 2 images from the other sheet, hope this helps! Appreciate it :)

Paul Newcome

Can you copy/paste the formula that is throwing the error directly from the sheet to here?

Jessica Howell

=COUNTIFS({Environmental Incident Tracking Range 1}"Leak", {Environmental Incident Tracking Range 3} 1,{Environmental Incident Tracking Range 4} IFERROR(YEAR(@CELL),0) =2020

Paul Newcome

There are commas missing from between the ranges and their criteria. You also need to make sure @cell is all lowercase as that little bit is case sensitive.

=COUNTIFS({Environmental Incident Tracking Range 1}, "Leak", {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)

Jessica Howell

So it shows that the formula is working, but it is not populating anything

Does it matter that the field for Leaks or Additions is a drop down?

Paul Newcome

Is it multi-select, and is the text exactly "Leaks", or are the other words included?

Jessica Howell

Single- select

My apologies, the drop-down option for leak is "Leak / Fuite", which i have now adjusted but still not working

Paul Newcome

Ok. Try this...

=COUNTIFS({Environmental Incident Tracking Range 1}, CONTAINS("Leak", @cell), {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)

Jessica Howell

Unfortunately still no.

I have tried a few others myself and cant seem to figure it out. Which line in the formula pulls the Quarter?

Paul Newcome

That would be something you would have to tell me. Which range contains the quarter?

Jessica Howell

It is range 3, which i have included in the formula

Paul Newcome

Ok. And exactly what is in that range on the source sheet?

Jessica Howell

it is fed from another sheet, the options are 1, 2, 3 or 4 (4 quarters in the year)

Paul Newcome

Ok. Going all the way back to the origin of the quarter, can you provide all formulas/cell links that get it onto the source sheet that we are referencing?