Formulas to count including Unique Numbers

Marcelo Branco

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

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,344
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,344
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,085,429
Messages
5,383,622
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top