Formulas to count including Unique Numbers

Marcelo Branco

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

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
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
Joined
Aug 23, 2010
Messages
16,371
To use an OR condition in an array formula you should add the conditions, for example

=SUM(IF(FREQUENCY(IF($D$2:$D$14="NO",IF((E2:E14="REL")+(E2:E14="AD"),IF(F2:F14="COL",MATCH(A2:A14,A2:A14,0)))),ROW(A2:A14)-ROW(A2)+1),1))
Ctrl+Shift+Enter

M.
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
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
Joined
Aug 23, 2010
Messages
16,371
Graham,

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

M.
 

Forum statistics

Threads
1,089,213
Messages
5,406,883
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top