Can you count unique cells that match criteria in another sheet?

MKRS

Hi Kelly,

I've been able to make it work….

=COUNT(DISTINCT(COLLECT({Case No}, {Country}, [Column2]17))) ([Column2]17 is a cell with a country's name in it), I'm pretty sure you gave me this formula but it did not work, so may be the combination of the value column and closing and opening the app got it to work (Smartsheets can be temperamental with formulas I find!

Thanks for your patience and support…it's been appreciated!

Kelly Moore

@MKRS

I'm not sure what is going on with your dataset. For these trials, please start completely fresh with your cross sheet references and build the formula by re-building the cross sheet reference. I'm wondering if the reference inadvertently didn't select the entire Country column?

=COUNT(DISTINCT(COLLECT({Case No},{Country},@cell="Italy")))

If the above didn't work after completely rebuilding the Country cross reference, try this

=COUNT(DISTINCT(COLLECT({Case No},{Country},CONTAINS("Italy", @cell))))

@mention me to help alert me to your email.

Kelly

Kelly Moore

Hey @MKRS

I didn't see your comment above (it had moved to the next page). Yes, I had suggested using the @row instead of hard-coding in Italy. The @row would give you the ability to see all the distincts by country assuming that your Sheet 2 is a type of metrics sheet where you have called out the Country names on rows

I didn't ask before, but if you do need an evergreen way to keep up with distinct Counts and possibly additional new country names, without wanting to update a metrics sheet with new country names, you can build a report. The formula you would then need instead is a way to tag a row in your source sheet as distinct. An easy way to do this is to look for the first instance of a value, ie your Case No, using either the Created column or Row Id column as markers down the sheet. In the report, you would Group by Country and count the helper column. Whether a report will work depends what you want to do with the data afterwards.

Assuming a checkbox column as Distinct helper in your source sheet (I used the Row ID system column but Created would work):

=IF(COUNTIFS([Case Number]:[Case Number], <>"",Country:Country, Country@row, [Row ID]:[Row ID], @cell<=[Row ID]@row)=1,1)

Just an option if you only want to display the data.

Kelly