Formulas to count including Unique Numbers

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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,317
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,317
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,082,382
Messages
5,365,129
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top