# Count unique values if adjacent cell meets criteria

1. ## Count unique values if adjacent cell meets criteria

Hi all,

I need a little help with perfecting my formula. In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG

At the moment I have a formula as below:

=SUMPRODUCT(--(B:B=\$D2),--(A:A<>""))

this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

I need some help modifying my formula, or ideas on a solution. I have attached the sample workbook below for your convenience.

Book1.xlsx

Any help is very much appreciated in advance.

Many thanks.

2. ## Re: Count unique values if adjacent cell meets criteria

Not sure if your results are as per expectation

There are 4 unique values for 666 and 2 for 777

Try
=SUMPRODUCT((\$B\$2:\$B\$10=D2)/COUNTIFS(\$A\$2:\$A\$10,\$A\$2:\$A\$10&"",\$B\$2:\$B\$10,\$B\$2:\$B\$10&""))

3. ## Re: Count unique values if adjacent cell meets criteria

Sorry my bad, yes should be 4 for 666 but 1 for 777 because I would like to ignore blanks in column A. Is that possible? Thanks for your reply!

4. ## Re: Count unique values if adjacent cell meets criteria

Try

=SUMPRODUCT((\$A\$2:\$A\$10<>"")*(\$B\$2:\$B\$10=D2)/COUNTIFS(\$A\$2:\$A\$10,\$A\$2:\$A\$10&"",\$B\$2:\$B\$10,\$B\$2:\$B\$10&""))

5. ## Re: Count unique values if adjacent cell meets criteria

Perfect! Thanks a lot!!! I'll mark this thread as solved and add a reputation to you.

6. ## Re: Count unique values if adjacent cell meets criteria

Quick question if you are still online: What does the &"" do in the COUNTIFS? I can understand first half of the formula you wrote. Would be great if I know how it works!

7. ## Re: Count unique values if adjacent cell meets criteria

It ensures the blank cells do not give you a #DIV/0 error

8. ## Re: Count unique values if adjacent cell meets criteria

...deleted

There are currently 1 users browsing this thread. (0 members and 1 guests)