CountIF one of multiple criteria are met within the current year.

Kris V. Le · 10/15/21

Good Afternoon. I'm trying to get a count if a specific event is documented to occur within any row in specific columns. This is where I'm at, although unsuccessfully....

=COUNTIFS({Range 1}, "Value1", {Range 2}, "Value 1", {Date Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))))

I need the sheet to count if "Value 1" appears in either "Range 1" or "Range 2" columns. Not that it must appear in both. I also need it to count of the date the value was generated was within the current year. I know the IFERROR function works, I just don't think I have the whole thing set up correctly. Thanks in advance!

Kris V. Le · 10/18/21

https://community.smartsheet.com/discussion/comment/308057#Comment_308057

Kelly, thank you very much! That appears to have solved the issue.

Kimberly Loveless · 10/15/21

Try this one...

= COUNTIFS(OR({Range 1} = "Value 1", {Range 2} = "Value 1"), {Date Range 1},IFERRIOR(YEAR(@cell),0)=YEAR(TODAY()))

Now one thing is this is what will work if Range 1, Range 2, and Date Range 1 are cross sheet references if not then those references in the formula will be slightly different

Kris V. Le · 10/15/21

I'm getting an "Invalid Operation" Error on this format.

Kelly Moore · 10/17/21

Hey @Kris V. Le

Because the 'OR' is occurring between multiple ranges and not choices within a single range you need to add the COUNTIFS together.

= COUNTIFS({Range 1},"Value 1", {Date Range 1},IFERROR(YEAR(@cell),0)=YEAR(TODAY())) + COUNTIFS({Range 2},"Value 1", {Date Range 1},IFERROR(YEAR(@cell),0)=YEAR(TODAY()))

Does this work for you?

Kris V. Le · 10/18/21

https://community.smartsheet.com/discussion/comment/308057#Comment_308057

Kelly, thank you very much! That appears to have solved the issue.

CountIF one of multiple criteria are met within the current year.

Best Answer

Answers

Help Article Resources

Categories