Countifs multi select column and year column with contains

kortneymorrison

Hello!

I have a multi-select column from which I am able to get a summary formula to count correctly, but every attempt I've made to account for the year from a second column has returned errors of some kind. The Year column has a formula in it as well: =YEAR([Contract Fully Executed Date]@row)

This is the formula that provides the count of rows that have "Production Planning", Production (on-site)" and "Production Virtual" - and now I would like to only return results if the Year column is 2024. I'm open to using the Contract Fully Executed Date column in the formula instead of the Year 'helper' but my attempts at that formula were even worse.

=COUNTIFS([Support Needed]:[Support Needed], OR(CONTAINS("Production Planning", @cell), CONTAINS("Production (on-site)", @cell), CONTAINS("Production Virtual", @cell)))

Thank you for your help - this is my first post!

Dylan T

Hi @kortneymorrison,

Try adding the following statement into your countifs statement:

[Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = Year

The Year value you're matching to can be hardcoded, a reference so a value in a Sheet Summary field, or somewhere else on the sheet. Here is an example of this statement I use in a countifs formula on one of my metric sheets, where the year is a field on the metric sheet:

{Submitted MO Date}, IFERROR(YEAR(@cell), 0) = $Year$1

Hope this works for you!

~ Dylan

kortneymorrison

Hi, Dylan - thanks for your help, but I couldn't get that to work. I adjusted my formula slightly and I'm still struggling. Will you have a look:

=COUNTIFS([Support Needed]:[Support Needed], CONTAINS("Contracting", @cell), (year:year, "2024"))

I simplified the summary formula which is going to be better in the long run, but I still need to return the number of times the dropdown for Support Needed contains "Contracting" AND the year column is 2024.

Thanks again

Kortney

Andrée Starå

Hi @kortneymorrison

I hope you're well and safe!

Try something like this.

=COUNTIFS([Support Needed]:[Support Needed], CONTAINS("Contracting", @cell), Year:Year, IFERROR(YEAR(@cell), 0) = "2024")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!