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))
Best Answer
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:$DFormula for F
=IF(COUNTIF(E$1:E2,E2)=1,1,0)( this is example fopr cell( F2)