Formulas to count including Unique Numbers

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
75
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
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
75
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
Joined
Aug 23, 2010
Messages
16,371
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
Joined
Feb 17, 2018
Messages
75
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.
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
75
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
Joined
Feb 17, 2018
Messages
75
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,265
Messages
5,485,758
Members
407,512
Latest member
PearceK

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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top