COUNTIF excluding duplicates

I

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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
I can do it with a couple of worker columns:

ABCDEFHIJ
NumbConcatenateCol:ECol:HResult
44YESQUIETYESQUIET
88YESNOTIFICATIONYESNOTIFICATION
88PERHAPSQUIETPERHAPSQUIET
88NONOTIFICATIONNONOTIFICATIONx
22NONOTIFICATIONNONOTIFICATIONx
88NONOTIFICATIONNONOTIFICATIONx
22YESQUIETYESQUIET
88PERHAPSQUIETPERHAPSQUIET
44NOQUIETNOQUIET
12NONOTIFICATIONNONOTIFICATIONx

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Count Duplicates[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
inharmony222​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2
and down
[/TH]
[TD="align: left"]=A2&E2&H2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2
and down
[/TH]
[TD="align: left"]=COUNTIFS($B$2:B2,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2
[/TH]
[TD="align: left"]=COUNTIFS(E2:E1000,"NO",H2:H1000,"NOTIFICATION",C2:C1000,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
244YESQUIET3
388YESNOTIFICATION
488PERHAPSQUIET
588NONOTIFICATION
622NONOTIFICATION
788NONOTIFICATION
822YESQUIET
988PERHAPSQUIET
1044NOQUIET
1112NONOTIFICATION
Sheet
 
Upvote 0
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!

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

ABCDEFGHIJ
Result
YES QUIET
YES NOTIFICATION
PERHAPS QUIET
NO NOTIFICATION
NO NOTIFICATION
NO NOTIFICATION
YES QUIET
PERHAPS QUIET
NO QUIET
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Numb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Yes/NO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]12[/TD]

</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
Click to expand...
 
Upvote 0
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!
 
Upvote 0
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 ;)
 
Upvote 0
Like this?
Formula must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHIJ
1NumbYes/NOResult
244YESQUIET3
388YESNOTIFICATION
488PERHAPSQUIET
588NONOTIFICATION
622NONOTIFICATION
788NONOTIFICATION
822YESQUIET
988PERHAPSQUIET
1044NOQUIET
1112NONOTIFICATION
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Find Duplicate Values using a Criteria
Replies
2
Views
183
Eric W
Eric W
T
  • Solved
take a column of daily transaction numbers with duplicates and show in different table with its corresponding date and no duplicates
2
Replies
13
Views
526
Dave Patton
D
S
  • Solved
How to count only unique values from a column in a COUNTIFS
2 3
Replies
21
Views
432
Fluff
Fluff
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
305
Kaper
Kaper
J
  • Question Question
CountIFS and Sumif based on duplicate value in other columns
Replies
3
Views
295
jennybell
J

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top