Count distinct values using Month, Year from another sheet

Justin Mauzy

I need to count the unique values from another sheet using MONTH and YEAR. Here is the formula I have:

=COUNTIFS(DISTINCT(COLLECT({[2021] Case Cleaning Schedule Range 1}, {[2021] Case Cleaning Schedule Range 2}, MONTH(@cell) = 1, {[2021] Case Cleaning Schedule Range 2}, YEAR(@cell) = 2022)))

A few details:

• I am referencing another sheet [2021] Case Cleaning Schedule
  • Range 1 = Zone: column
  • Range 2 = Service Date: column
• I want to count the unique values in the Zone: column

Thanks for the help in advance.

Devin Lee

Hey @Justin Mauzy,

For whatever reason COLLECT with MONTH(@cell) and YEAR(@cell) has some bugs so instead I used VALUE combined with LEFT and RIGHT.

=COUNT(DISTINCT(COLLECT(Zone:Zone, [Service Date]:[Service Date], AND(VALUE(LEFT(@cell, 2)) = 1, VALUE(RIGHT(@cell, 2)) = 22))))

If it wasn't buggy something like this would work but for whatever reason it doesn't.

=COUNT(DISTINCT(COLLECT(Zone:Zone, [Service Date]:[Service Date], AND(MONTH(@cell) = 1, YEAR(@cell) = 2022))))

Another option is to add two columns and make them MONTH([Service Date]@row) and YEAR([Service Date]@row) as column formulas and then something like this works.

=COUNT(DISTINCT(COLLECT(Zone:Zone, Month:Month, @cell = 1, Year:Year, @cell = 2022)))

Devin Lee

Hey @Justin Mauzy,

For whatever reason COLLECT with MONTH(@cell) and YEAR(@cell) has some bugs so instead I used VALUE combined with LEFT and RIGHT.

=COUNT(DISTINCT(COLLECT(Zone:Zone, [Service Date]:[Service Date], AND(VALUE(LEFT(@cell, 2)) = 1, VALUE(RIGHT(@cell, 2)) = 22))))

If it wasn't buggy something like this would work but for whatever reason it doesn't.

=COUNT(DISTINCT(COLLECT(Zone:Zone, [Service Date]:[Service Date], AND(MONTH(@cell) = 1, YEAR(@cell) = 2022))))

Another option is to add two columns and make them MONTH([Service Date]@row) and YEAR([Service Date]@row) as column formulas and then something like this works.

=COUNT(DISTINCT(COLLECT(Zone:Zone, Month:Month, @cell = 1, Year:Year, @cell = 2022)))

Justin Mauzy

That work perfectly! Thank you for the help.