Skip to Content

Not Count Doubles ID

Hi community,

Simple question, need your help.

My Request return me some ID in a table. Like here:

SD010 test1

SD011 test2

SD012 test3

SD010 test2

SD0113 test4

SD010 test34

I would like to count not duplicate SDs

So I need to count only SD011+SD012+SD113 = 3 (SDs)

Formula like =Count([SD]) return 6

Formulf like =Count([SD];DISTINCT) return also 6

I need =Count(something SD) need to return 3

How it resolve by formula ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jul 18, 2017 at 04:30 PM

    use this.

    =Sum(If(Count([ID];All) In ([ID])>1) Then 0 Else 1)

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 18, 2017 at 05:14 PM

    Thanks Amit it works,

    But When I tried to use this formula for my table like:

    Service Name Total Count

    Service_Test_1 10

    Service_Test_2 10

    Service_Test_3 10

    So the Count result is same for each service in Total_Count column, this is not correct because for each service_name count result is different

    =Sum(If(Count([ID];All) In ([ID])>1) Then 0 Else 1) need to be different.for each Service in Service_Name column

    How it could be fixed ?

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 20, 2017 at 09:51 AM

    I've checked my logic and found some difficulties with my report.

    So I've got 3 different queries.

    The first query get values with some IDs

    The second query get other values with some IDs

    The third query get other value with some IDs

    And finally I've got a table with merged IDs from all of these queries.

    The problem is that IDs may be same between these queries.

    So I need to Count IDs but not to be same from other queriy.

    And here below is my needable table

    (Total IDs from Query-1 and not same In Query-2,3) (Query-1 IDs) (Query-2 IDs) (Query-3 IDs)

    6 100 10 10

    7 154 12 12

    89 167 18 19

    So my question is how to use =Count or some other function to Count Query-1 IDs wich is not same in Query-2 and Query-3 IDs ?

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 21, 2017 at 12:20 PM

    I've resolved my problem. It's Ok now.

    By the way thanks for your help.

    Add comment
    10|10000 characters needed characters exceeded