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

MKRS

Currently I'm getting the result of '1' for this formula which is not correct: =COUNT(DISTINCT(COLLECT({Column 1}, {Column 2}, [Country]1)))

What am I doing wrong?

NB. Column 1 & Column 2 are in a different sheet.

Kelly Moore

Hey @MKRS

How are the CRM# populated in your sheet. As a test, can you add a text/number helper column to that source sheet and populate that column using this formula:

=VALUE([CRM #]@row)

I'm hoping to try to force all the values into a numeric value. The leading apostrophe will make it a textstring.

Then change your formula in the 2nd sheet so that the helper column is the Case# range

Kelly

Kelly Moore

Hey @MKRS

How many names are in your Country1 cell? If only one, then there would be only one distinct name for that.

Perhaps you need this formula instead?

=COUNTIFS({Column 2}, [Country]1)

If you need your formula to be more dynamic, you might be able to use =COUNTIFS({Column 2}, Country@row)

If this doesn't work let me know. Screenshots (remove sensitive info or make a quick demo file) will help us see why you are receiving unexpected results.

Kelly

MKRS

There will be multiple countries and for each country multiple cases some of which will be duplicated, so I need to do a count of the number of unique cases for a country. I can do this where this is all in the same sheet but the formula does not work when I try to reference other sheets.

Example:

Kelly Moore

Hey @MKRS

In the screenshot above, I only see 2 or 3 distinct of anything, depending on what your criteria is.

If you ask for Distinct 123, 145, or 158 there are only 2 distinct values for any of those. If you ask for country name, there are only 3. I recognize this is only a snippet of the whole sheet. In your original formula, did you make your criteria too specific so that it filtered out to only one result? I tested your formula against your screenshot above and it returns the values I expected from the screenshot.

Kelly

Kelly Moore

Hey @MKRS

Try this

=COUNT(DISTINICT(COLLECT({Column 2},{Column 1},Country@row)

This should count the number of different instances you have per country

Is this what you were needed?
Kelly

MKRS

Hi Kelly,

There will be multiple countries and then within each country multiple case numbers some of which will be duplicated, what I need is for it to count the number of unique instances in Sheet 1 Column 2 when the country in Sheet 1 Column 1 is Italy. The formula and result need to sit in a different sheet.

Example:

Sheet 1 Column 1	Sheet 1 Column 2	Sheet 2
Italy	123	Italy =	4
Germany	123
Italy	144
Spain	145
Spain	145
Italy	145
Italy	158
Italy	158
Italy	158
Germany	158
Germany	158

I can get this to work when all are in the same sheet, but if I reference another sheet it stops working and I get the answer of 1, when based on this example data it should be 4.

Kelly Moore

@MKRS

Did you try the formula I gave you?

=COUNT(DISTINICT(COLLECT({Column 2},{Column 1},Country@row)

If you want it specific to Italy then

=COUNT(DISTINICT(COLLECT({Column 2},{Column 1},@cell="Italy")

In the example above, your answer will be 4

Kelly

MKRS

Unfortunately it's giving me an #Unparseable error

Formula used is: =COUNT(DISTINICT(COLLECT({Country},{Case#},@cell="Italy")))

Kelly Moore

@MKRS

Sorry, I had typos. Are you sure that your 'Italy' appears in the {Case#} column. It seems it should be reversed. You are counting the column where the 123 and 258's appear - not the column with the country names.

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

MKRS

To clarify, I need to count the number of unique case #s where the country is Italy. The data set has multiple entries of 'Italy' and there will be duplicate entries of the case case #s.

The answer from the formula you suggested is 1 (using the data in my sheet, which is incorrect as there are 1017 instances of Italy in Sheet 1 Column 1 of which there are 362 unique case #s, so I'm wondering if it's the distinct & collect that is throwing things?

Kelly Moore

Hey @MKRS

If you haven't tried this, as I suggested, please do.

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

This says: Collect and count the distinct Case # where the country = Italy.

Also wondering, for the Case# - are they all appearing as numbers (right justified in their column?). If you see values in the column that all appear as numbers yet they are both right and left justified in the same column, smartsheet would see these as different from one another even if they appeared to us as the same 'number'.

Kelly

MKRS

Hi Kelly,

I've used the formula exactly as you have written and it's showing a result of 1, I've also checked the numbers and as the entries are added via a form I was able to put in a number validation at start of the data being collected, so these all appear to be accurate.

Kelly Moore

@MKRS

Could you share a screenshot of your actual data in the source sheet? To verify, your Case# range and your Country ranges are single columns each? Your Case# range has the numbers in it and {Country} has the names of the country. 'Italy' is written just as it appears in the data set?

I tested the formula in 2 of my test sheets and the formula is correct.

Try this, just as a test

=COUNT(DISTINCT(COLLECT({Case#},{Country},@cell<>"")))

MKRS

Hi Kelly,

The result when using =COUNT(DISTINCT(COLLECT({Case#},{Country},@cell<>""))) is '1' again.

Screenshot of a the data in their columns:

Interestingly, if I click on the Case # cell it shows a ' before the number…..which I think could be what's causing some issues??

Kelly Moore

Hey @MKRS

How are the CRM# populated in your sheet. As a test, can you add a text/number helper column to that source sheet and populate that column using this formula:

=VALUE([CRM #]@row)

I'm hoping to try to force all the values into a numeric value. The leading apostrophe will make it a textstring.

Then change your formula in the 2nd sheet so that the helper column is the Case# range

Kelly

MKRS

Hi Kelly,

Many thanks for sending through the helper column suggestion which I have implemented.

Unfortunately this did not change the result (I did update the formula to look at the new column)

Stripping this down, this formula did work =COUNT(DISTINCT({Case No})) showing a result of 2111, so it is looking for the unique instances of the cases, so its where the case no meets the criteria for the country that needs resolving.

Any ideas?