Excel Formula to give unique countif

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
498
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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))
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
Glad you figured it out & thanks for the feedback
 
Upvote 0
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
BCD
3144317793
42752No Change
52752No Change
62752No Change
73344533448
8
93344533448
103344533448
11744No Change
1212341244
Sheet1
Cell Formulas
RangeFormula
D3{=SUM(--(FREQUENCY(IF(B3:B16<>"No Change",C3:C16),C3:C16)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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