Excel count unique values in a column  based on data in another column

In a administration sheet used for software testing I want to count certain values for an overview.
I need some help for this situation:

A test-checklist leads to a number of issues. In this case the issues are related to each other, so  only one bug report is registered and 3 issues are inserted into this finding
On  my own adminsheet I note down the bug number next to the testresult.

My admin sheet:

Column A = Test nr
Column B = Test result (ok or nok)
Column C = BUG nr
Column D = Issue seq. number
Column E = Status of issue (open, ok or nok)

1  | ok
2  | nok | 1234 | 1 | open
3  | ok
4  | nok | 1234 | 2 | open
5  | nok | 1234 | 3 | ok
6  | nok | 2345 | 1 | open
7  | ok
8  | nok | 2345 | 2 | nok
9  | nok | 3456 | 1 | ok
10 | nok | 4567 | 1 | nok

I need to know how many bugs / issues are on the developers desk

To count the number of registered bugs (unique numbers)
=SOM(ALS(INTERVAL(C1:C10;C1:C10)>0;1))
=SUM(IF(FREQUENCY(C1:C10;C1:C10)>0;1))
(4)

To count the number of not solved issues: (open or nok)
=AANTAL.ALS(E1:E10;"open")+AANTAL.ALS(E1:E10;"nok")
=COUNT.IF(E1:E10;"open")+COUNT.IF(E1:E10;"nok")
(5)

Now I like to count how many unique bug-numbers are still not solved
The answer should be 3 in this example (nr 1234, 2345, 4567)

I hope to get a formula as a result on this question.

Thanks in advance!
Start Free Trial