COUNTIF excluding duplicates

inharmony222

New Member
Joined
Sep 19, 2019
Messages
4
Hello,

I am just starting to learn formulas in Excel. I have created a "count if" formula looking at multiple columns but I need to exclude duplicate rows based on a value (being a number) listed in column A.

My current formula looks like this: =COUNTIFS(E2:E1000,"NO",H2:H1000,"NOTIFICATION")

How can I add in a way to make it only count one of any # in column A that have a duplicate #.

Oh, I have also added conditional formatting already to column A so it formats color fill to any duplicates after the first, if that helps with a formula.

Thanks!
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
331
I can do it with a couple of worker columns:

ABCDEFHIJ
1NumbConcatenateCount DuplicatesCol:ECol:HResult
24444YESQUIET1YESQUIET3
38888YESNOTIFICATION1YESNOTIFICATION
48888PERHAPSQUIET1PERHAPSQUIET
58888NONOTIFICATION1NONOTIFICATIONx
62222NONOTIFICATION1NONOTIFICATIONx
78888NONOTIFICATION2NONOTIFICATIONx
82222YESQUIET1YESQUIET
98888PERHAPSQUIET2PERHAPSQUIET
104444NOQUIET1NOQUIET
111212NONOTIFICATION1NONOTIFICATIONx
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
inharmony222

Worksheet Formulas
CellFormula
B2
and down
=A2&E2&H2
C2
and down
=COUNTIFS($B$2:B2,B2)
J2
=COUNTIFS(E2:E1000,"NO",H2:H1000,"NOTIFICATION",C2:C1000,1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
Here is another way without helper columns.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHIJ
1NumbYes/NOResult
244YESQUIET
388YESNOTIFICATION
488PERHAPSQUIET
588NONOTIFICATION
622NONOTIFICATION
788NONOTIFICATION
822YESQUIET
988PERHAPSQUIET
1044NOQUIET
1112NONOTIFICATION
Sheet
 

inharmony222

New Member
Joined
Sep 19, 2019
Messages
4
I went with this one because I have never used helper columns, although I might want to learn.
It looks like it works!

Thanks for the quick replies!

Here is another way without helper columns.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

ABCDEFGHIJ
1Numb Yes/NO Result
244 YES QUIET 3
388 YES NOTIFICATION
488 PERHAPS QUIET
588 NO NOTIFICATION
622 NO NOTIFICATION
788 NO NOTIFICATION
822 YES QUIET
988 PERHAPS QUIET
1044 NO QUIET
1112 NO NOTIFICATION

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:60px;"><col style="width:33px;"><col style="width:64px;"><col style="width:64px;"><col style="width:24px;"><col style="width:38px;"><col style="width:108px;"><col style="width:32px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
J2{=SUM(IF(FREQUENCY(IF($E$2:$E$11="NO",IF($H$2:$H$11="NOTIFICATION",$A$2:$A$11)),$A$2:$A$11),1))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

inharmony222

New Member
Joined
Sep 19, 2019
Messages
4
I thought I could figure out how to edit this and use it for another one that is simpler but I'm stumped again lol!

Similar type of thing, current formula is: =COUNTIFS(E2:E1001,"NO")

But I also want this one to only count one of any # in column A that have a duplicate #.

I assumed once I had a formula for the more complicated one I could just remove part of the formula looking at the "count if" for H but I can't seem to get it to work lol!

Thanks!
 

inharmony222

New Member
Joined
Sep 19, 2019
Messages
4
Or on second thought, an even better thing would be if I could use this formula

{=SUM(IF(FREQUENCY(IF($E$2:$E$11="NO",IF($H$2:$H$11="NOTIFICATION",$A$2:$A$11)),$A$2:$A$11),1))}

But count everything in column H that does NOT say "NOTIFICATION".
Is that possible to do?

What I was forgetting that if I do the other way it will be counting the notifications too which would not be an accurate # for this purpose ;)
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
Like this?
Formula must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHIJ
1NumbYes/NOResult
244YESQUIET
388YESNOTIFICATION
488PERHAPSQUIET
588NONOTIFICATION
622NONOTIFICATION
788NONOTIFICATION
822YESQUIET
988PERHAPSQUIET
1044NOQUIET
1112NONOTIFICATION
Sheet
 

Forum statistics

Threads
1,082,131
Messages
5,363,337
Members
400,726
Latest member
Shahzad Taimoor

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