Formulas to count including Unique Numbers

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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
Maybe this..

M
1
List​
2
3​
3
6​
4

Array formula in M2 copied down
=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH(A$2:A$14,IF((C$2:C$14<>"")+(D$2:D$14="Yes"),A$2:A$14),0)),MATCH(A$2:A$14,A$2:A$14,0)),ROW(A$2:A$14)-ROW(A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(M$2:M2))),"")
Ctrl+Shift+Enter

M.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
76
Thanks M, works a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.

Thanks again.
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
76
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.
Hi M,

As you have done for one of my other formulas, can you assist with also getting these into a list instead of just a number ?

Thanks
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Thanks M, works a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.

Thanks again.
All you have to do is changing
=IFERROR(INDEX(A$2:A$14,...

to
=IFERROR(INDEX(B$2:B$14,...

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Hi M,

As you have done for one of my other formulas, can you assist with also getting these into a list instead of just a number ?

Thanks
See formula in post 31 - same idea

Try to do it yourself - if you have problems come back here

M.
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
76
Hi M,

Tried it but it's not right.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")

This gives me the User ID of 9 which is correct.

But

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(N$2:N2))),"")

This gives me the name Trev but it should be Oliver as this is the name for User ID 9.

Any ideas what i'm doing wrong ?

Thanks[/FONT]<strike>
</strike>
[/FONT]
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Your formula seems perfect to me. Trev and Oliver satisfy the conditions.

M.
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
76
Your formula seems perfect to me. Trev and Oliver satisfy the conditions.

M.
Hi M,

This isn't right.

One of the earlier formulas was the following :-

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="No"),A2:A14),0)),MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))

The result for this was 1. This is because there is 1 generic user ID where UTC=Yes and Last Logon is Blank. This is correct. Next I wanted a list of the results instead of a number.

This formula:-
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")

This gives me number 9 which is also correct as this is the user that meets the criteria on the above formula. What I tried to do with the following formula was pull the next cell to number 9 which was the name Oliver. However I get the name Trev instead but this person does not meet the criteria as their User ID is 4 and there is also another 4 that does not meet the criteria.

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(O$2:O2))),"")

So Trev should never be part of this formula as there are 2 x User ID 4's and one does not meet the criteria.

Hope this makes sense.

Thanks as always.[/FONT]<strike>
</strike>
[/FONT]
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
76
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.
Hi M,

How can I add another part to this formula so when it looks down E2:E14 it counts both Rel & AD ?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,102,867
Messages
5,489,367
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top