Excel Formula to give unique countif

[nirvehex]

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]

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:

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

 

[Fluff]

Have a look here
https://exceljet.net/formula/count-unique-text-values-with-criteria
or here
https://exceljet.net/formula/count-unique-numeric-values-with-criteria

depending whether your values are text or numeric

 

[DataBlake]

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?

 

[DataBlake]

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

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

 

[nirvehex]

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.

 

[nirvehex]

I tried entering this code, but it give me an N/A error.

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

 

[nirvehex]

Actually there was a typo. This code works:

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

 

[Fluff]

Glad you figured it out & thanks for the feedback

 

[DataBlake]

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:

Book1
	B	C	D
3	1443	1779	3
4	2752	No Change
5	2752	No Change
6	2752	No Change
7	33445	33448
8
9	33445	33448
10	33445	33448
11	744	No Change
12	1234	1244
Sheet1

Cell Formulas
Range	Formula
D3	{=SUM(--(FREQUENCY(IF(B3:B16<>"No Change",C3:C16),C3:C16)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.