# Thread: Formulas to count including Unique Numbers Thanks:  10 Post #5297486 (1)Post #5293148 (1)Post #5294463 (1)Post #5293866 (1)Post #5295877 (1) Likes: 0

1. ## Re: Formulas to count including Unique Numbers

What's the difference from the 2nd query? They look identical ...

M.  Reply With Quote

2. ## Re: Formulas to count including Unique Numbers

Hi M,

Probably a bad example but he slight difference here is query 2 gave me a number of unique User ID's base on the criteria. This time i need to count all the user ID's that meet the criteria.

So query 2 result is 2 and they are Steve & Rich.

This time the answer is also 2 and again it's Steve & Rich.

However if I added another user under User id 3 with no logon data and UTC = "No" then this answer would be 3 but query 2 would still be 2 as query 2 was unique User ID's.

Thanks  Reply With Quote

3. ## Re: Formulas to count including Unique Numbers

Try

Array formula
=SUM(IF(A2:A14<>"",--ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="Yes"),A2:A14),0))))
Ctrl+Shift+Enter

M.  Reply With Quote

4. ## Re: Formulas to count including Unique Numbers

Hi M,

Works a treat.  Reply With Quote

5. ## Re: Formulas to count including Unique Numbers

 Next I would like to count the Unique User ID's based on the following criteria - Type="COL", Model=Rel",UTC="No". So in this case the number would be 4. User ID's 1,3,5,7. Then based on this information (Type="COL", Model=Rel",UTC="No") I need to count the total number of users. This would be 5 (Stephen, Brian,Steve,Simon & Darren)  Reply With Quote

6. ## Re: Formulas to count including Unique Numbers

First
Array formula
=SUM(IF(FREQUENCY(IF(\$D\$2:\$D\$14="NO",IF(E2:E14="REL",IF(F2:F14="COL",MATCH(A2:A14,A2:A14,0)))),ROW(A2:A14)-ROW(A2)+1),1))
Ctr+Shift+Enter

Second
Regular formula
=COUNTIFS(D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

M.  Reply With Quote

7. ## Re: Formulas to count including Unique Numbers

Thanks M, all working great.

If i wanted to add another ask in the second regular formula to look down the Last Logon and then count all users, where would the additional C2:C15,<>"" go as i tried to add it but keep getting errors.

=COUNTIFS(D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

Thanks.  Reply With Quote

8. ## Re: Formulas to count including Unique Numbers

=COUNTIFS(C2:C14,"<>",D2:D14,"No",E2:E14,"REL",F2:F14,"COL")

M.  Reply With Quote

9. ## Re: Formulas to count including Unique Numbers

Thanks M these are working great. I've tried them on the full data set and get exactly what i need.  Reply With Quote

10. ## Re: Formulas to count including Unique Numbers Originally Posted by Marcelo Branco 2nd query

Array formula
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="Yes"),A2:A14),0)),MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))
Ctrl+Shift+Enter

Does it work for you?

M.
M,

Is it possible to tweak the above formula to provide a list instead of just a number ? So say I put the formula in M1 it would list in M1, M2 etc,, the results of the query ?

Thanks  Reply With Quote

## User Tag List

#### Tags for this Thread

count, ids, query, rel, user #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•