# Thread: Excel Formula to give unique countif Thanks: 0 Likes: 0

1. ## Excel Formula to give unique countif

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  Reply With Quote

2. ## Re: Excel Formula to give unique countif

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))`  Reply With Quote

3. ## Re: Excel Formula to give unique countif

Have a look here
https://exceljet.net/formula/count-u...-with-criteria
or here
https://exceljet.net/formula/count-u...-with-criteria

depending whether your values are text or numeric  Reply With Quote

4. ## Re: Excel Formula to give unique countif

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?  Reply With Quote

5. ## Re: Excel Formula to give unique countif

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))`  Reply With Quote

6. ## Re: Excel Formula to give unique countif Originally Posted by BlakeSkate 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.  Reply With Quote

7. ## Re: Excel Formula to give unique countif

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!  Reply With Quote

8. ## Re: Excel Formula to give unique countif

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!  Reply With Quote

9. ## Re: Excel Formula to give unique countif

Glad you figured it out & thanks for the feedback  Reply With Quote

10. ## Re: Excel Formula to give unique countif Originally Posted by nirvehex 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:

BCD
3144317793
42752No Change
52752No Change
62752No Change
73344533448
8
93344533448
103344533448
11744No Change
1212341244

Sheet1

Array Formulas
CellFormula
D3{=SUM(--(FREQUENCY(IF(B3:B16<>"No Change",C3:C16),C3:C16)>0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself  Reply With Quote

## User Tag List

bn3bn4, equal, formula, unique, write 