# Formulas to count including Unique Numbers

#### Marcelo Branco

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

M.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Graham C1600

##### Board Regular
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

#### Marcelo Branco

##### MrExcel MVP
Try

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

M.

Hi M,

Works a treat.

#### Graham C1600

##### Board Regular
 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)

<tbody>
</tbody>
<strike></strike>

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

#### Marcelo Branco

##### MrExcel MVP
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.

#### Graham C1600

##### Board Regular
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.

#### Marcelo Branco

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

M.

#### Graham C1600

##### Board Regular
Thanks M these are working great. I've tried them on the full data set and get exactly what i need.

#### Graham C1600

##### Board Regular
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

1,102,202
Messages
5,485,319
Members
407,496
Latest member
PttrsnMrgn

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...