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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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>
 
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!

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
 
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

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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