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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
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
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.
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
60
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
60
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
60
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,090,368
Messages
5,414,042
Members
403,514
Latest member
Vivek pare

This Week's Hot Topics

Top