# Count distinct values using Month, Year from another sheet

✭✭✭✭✭

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.

• ✭✭✭✭✭

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)))
```

• ✭✭✭✭✭

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)))
```

• ✭✭✭✭✭

That work perfectly! Thank you for the help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!