# Formulas to count including Unique Numbers

#### Marcelo Branco

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

### 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
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
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
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
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
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
Your formula seems perfect to me. Trev and Oliver satisfy the conditions.

M.

#### Graham C1600

##### Board Regular
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
Hi M,

All sorted, my error. Got it working.

Thanks again.

#### Graham C1600

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