Filter by year

TerryVR

I am trying to count meetings based on the week number and year. I have the week number but can't figure out how to filter by year. As an example, my current formula for week #44 is

=COUNTIF({IT Meeting}, IFERROR(WEEKNUMBER(@cell), 0) = 44)

How do I filter for 2021 or 2022?

Paul Newcome

Try this...

=COUNTIFS({IT Meeting}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 44, IFERROR(YEAR(@cell), 0) = 2022))

TerryVR

That did it. Thank you Paul

Antonio Figueroa

Good Morning

You could use a helper column and add the below formula.

=VALUE(IF(AND(WEEKNUMBER([IT Meeting]@row) = 44, RIGHT([IT Meeting]@row, 2) = 21), 1, 0))

then just do a count if =1

TerryVR

I want to be able to count the number of non-blank cells based on the year in the IT Meeting column. In your example, all dates are in 2021. If there was a mix of 2021 and 2022 dates how can I count just the cells for 2022?

Paul Newcome

Try this...

=COUNTIFS({IT Meeting}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 44, IFERROR(YEAR(@cell), 0) = 2022))

TerryVR

That did it. Thank you Paul

Paul Newcome

Happy to help. 👍️