Skip to Content
0

Not Count Doubles ID

Jul 18, 2017 at 04:16 PM

45

avatar image

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 ?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

AMIT KUMAR
Jul 18, 2017 at 04:30 PM
0

use this.

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

Share
10 |10000 characters needed characters left characters exceeded
Pavel Prokoshev Jul 18, 2017 at 05:14 PM
0

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 ?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

i did not understand what is the issue.please explain in detail.

0

Ok, I'll try to check if it be clear I'll describe here. By the way thanks for your help

0
Pavel Prokoshev Jul 20, 2017 at 09:51 AM
0

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 ?

Share
10 |10000 characters needed characters left characters exceeded
Pavel Prokoshev Jul 21, 2017 at 12:20 PM
0

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

By the way thanks for your help.

Share
10 |10000 characters needed characters left characters exceeded