# Excel Formula to give unique countif

#### nirvehex

##### Active Member
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
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
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
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
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
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
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
Glad you figured it out & thanks for the feedback

#### BlakeSkate

##### Well-known Member
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>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />