COUNTIF(DISTINCT

Options

Hello Community, I am attempting to count unique numbers in one column if the code in the other column is "X". =COUNTIF(DISTINCT([Claim Number]:[Claim Number], [Contract Type]:[Contract Type], @cell = "LW"))

Claim numbers are formatted as letter and numbers. Each part work fine separately, but not together.

What am I doing wrong

Cheers,

Peter

Tags:

Best Answer

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/04/22 Answer ✓
    Options

    Here is a different approch

    • Collect all the Claim number rows with contract type "LW",
    COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")
    
    • select only the distinct ones,
    DISTINCT(...)
    
    • count them.
    COUNT(...)
    


    =COUNT(DISTINCT(COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")))
    

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    Distinct seems to only take one range as parameter.

    Break it in 2

    =COUNTIF(DISTINCT([Claim Number]:[Claim Number]), @cell = "LW")+COUNTIF(DISTINCT( [Contract Type]:[Contract Type]), @cell = "LW")

  • PeterLatcapital
    Options

    Hi Christian,

    First, thank you!

    I only need one column to be distinct [claim number] which is the one I want to count, if the other column meets the criteria [contract type] is "LW".

    Peter

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/04/22 Answer ✓
    Options

    Here is a different approch

    • Collect all the Claim number rows with contract type "LW",
    COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")
    
    • select only the distinct ones,
    DISTINCT(...)
    
    • count them.
    COUNT(...)
    


    =COUNT(DISTINCT(COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")))
    
  • PeterLatcapital
    Options

    Brilliant!!! It worked. Much appreciated. You made my day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!