Formulas to count including Unique Numbers

Graham C1600

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

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top