# Excel – How to count unique values in one column based on criteria in two other columns

microsoft-excel-2010

I am trying to to count the number of unique entries in column A where Column C says NO and column D says YES. However, I cannot devise a formula to do this based on two different criteria. How could I do something like this?

For instance, I want to know the number of unique countries that have viewed an event on-demand but not live, which would be 4 in the following example:

``````country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES
``````

From the formulas suggested in the answer below I have managed to get excel to accept the following formula but this does not return a value. The aim of this is to enter a 1 in column F if E is equal to 1. Any help would be appreciated:

``````=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))
``````

Would you consider adding a formula that simply comonbines values of both columns, and another that counts new/unique values?

Formula for E `=\$C:\$C&\$D:\$D`

Formula for F `=IF(COUNTIF(E\$1:E2,E2)=1,1,0)` ( this is example fopr cell( F2)

``````country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0
``````