# COUNTIFS Function

Options

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!

«13

• ✭✭✭✭✭✭
Options

=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?

• Options

@Paul Newcome Came up unparseable

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

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?

• ✭✭✭✭✭✭
Options

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

• Options

Single- select

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

• ✭✭✭✭✭✭
Options

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)

• Options

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?

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!