Excel Formula to give unique countif

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
470
Hi,

I'm trying to write a formula that says count unique values in the range of b3:B4 where bn3:bn4 does not equal no change.

Any idea how to write this?

Thanks,

Mark
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
470
Note - this also includes blank rows which I don't want to be included in the overall count.

This formula works when there are no blanks, but breaks when there are blank rows:
Code:
=SUM(IF("No Change"<>$BN$3:$BN$277, 1/(COUNTIFS($BN$3:$BN$277, "<>No Change", $B$3:$B$277, $B$3:$B$277)), 0))
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
616
Office Version
2016
Platform
Windows
does No Change have blanks? (assuming that is a named range, if not please explain what no change is)
or just the BN3:BN277 range?
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
616
Office Version
2016
Platform
Windows
oh nevermind. i see you mean that there is cell values that = "No Change" that you would like excluded. so maybe this? hard to tell without an example
ctrl + shift + enter must be used

Code:
=SUM(--(FREQUENCY(IF(B3:B227<>"No Change",BN3:BN227),BN3:BN227)>0))
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
470
does No Change have blanks? (assuming that is a named range, if not please explain what no change is)
or just the BN3:BN277 range?
Just the B range has potential blanks. BN has to equal not "No Changes" and B is the column I want to sum unique values. The B column sometimes has blanks and I don't want those included in the unique value count. I hope that makes more sense. Sorry for the confusion.
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
470
I tried entering this code, but it give me an N/A error.

Code:
=SUM(--(FREQUENCY(IF(B3:B277<>"",IF(BN2:BN277<>"No Change",MATCH(B3:B277,B3:B277,0))),ROW(B3:B277)-ROW(B3)+1)>0))
Trying to count unique values in column B where BN DOES NOT equal No Change. Column B has some blanks which I don't want included in the unique count. Also, column BN has blanks which should get excluded as well.

Thanks again!
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
470
Actually there was a typo. This code works:
Code:
=SUM(--(FREQUENCY(IF(B3:B277<>"",IF(BN3:BN277<>"No Change",MATCH(B3:B277,B3:B277,0))),ROW(B3:B277)-ROW(B3)+1)>0))
Thanks Fluff for the referral site code!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,402
Office Version
365
Platform
Windows
Glad you figured it out & thanks for the feedback
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
616
Office Version
2016
Platform
Windows
I tried entering this code, but it give me an N/A error.
did you try the code i provided? it should do this without whatever you added
i'll attach an example:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1443</td><td style="text-align: right;;">1779</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2752</td><td style=";">No Change</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2752</td><td style=";">No Change</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2752</td><td style=";">No Change</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">33445</td><td style="text-align: right;;">33448</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">33445</td><td style="text-align: right;;">33448</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">33445</td><td style="text-align: right;;">33448</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">744</td><td style=";">No Change</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1234</td><td style="text-align: right;;">1244</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">B3:B16<>"No Change",C3:C16</font>),C3:C16</font>)>0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Forum statistics

Threads
1,082,510
Messages
5,365,993
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top