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

Options
MKRS
MKRS ✭✭
edited 05/13/24 in Formulas and Functions

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.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

«1

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    MKRS ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    MKRS ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    @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
    MKRS ✭✭
    Options

    Unfortunately it's giving me an #Unparseable error

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    @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
    MKRS ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    MKRS ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    @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
    MKRS ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    MKRS ✭✭
    Options

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!