# Formulas to count including Unique Numbers

#### Marcelo Branco

##### MrExcel MVP
If the possible values in E2:E14 are only REL and AD, as shown in your data sample, all you have to do is delete the condition IF(E2:E14 = "REL" from the formulas.

M.

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Graham C1600

##### Board Regular
If the possible values in E2:E14 are only REL and AD, as shown in your data sample, all you have to do is delete the condition IF(E2:E14 = "REL" from the formulas.

M.
Hi M,

Thanks for the reply.

In my actual data there are numerous values in E2:E14. I just tried to keep it as simple as possible when I was getting all the formulas to work.

Thanks

#### Marcelo Branco

##### MrExcel MVP
To use an OR condition in an array formula you should add the conditions, for example

Ctrl+Shift+Enter

M.

#### Graham C1600

##### Board Regular
Thanks M worked a treat.

#### Graham C1600

##### Board Regular
Maybe this..

 M​ 1​ List​ 2​ 3​ 3​ 6​ 4​

<tbody>
</tbody>

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.
Hi M,

Quick query which I cannot figure out.

Everything is working fine however I've been asked to provide additional data now which I cannot get the formula to work for.

So
C\$2:C\$14<>"" part of the formula returns any cells from C2 to C14 that are blank. I also need to add column B2 to B14 that are also blank. I know on my original spreadsheet they had names in but now in my master spreadsheet they have different data. I've tried the following but it does not return the correct data :-
IF((B\$2:B\$14<>"")+(C\$2:C\$14<>"")+(D\$2:D\$14="Yes").

Any ideas what the issue is ?

Thanks

#### Marcelo Branco

##### MrExcel MVP
Graham,

This thread is getting too long...
New data? I suggest you create a new thread - new data sample along with expected results.

M.

1,089,475
Messages
5,408,462
Members
403,208
Latest member
JFoley182