2
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))
if VBA is OK then a "Collection"-object provides an easy possibility to count the number of unique entries – OlimilOops – 2013-12-06T16:09:30.373
Thanks for the comment. There will be lots of identical entries. For example GB NO YES will appear many times – Andrew – 2013-12-06T16:56:13.453
I had a quick look at VBA but i have never used this before. Whilst it looks very powerful it would be great if there was a simple formula for this – Andrew – 2013-12-06T17:05:08.077
Just to clarify what I need here: There will be many individuals from each country with the same or different criteria. In a separate worksheet is a summary of key details which includes the number of unique countries that have watched the event. In that summary sheet I need a simple number to show how many unique countries have watched the event on demand only. And so I need a formula to get this number. – Andrew – 2013-12-06T17:12:38.380
the VBA-code below does exactly what you want. but if you want to have pure excel-functions, you must use array functions (F2 / Ctrl+Shift+Enter) SUM, IF, FREQUENCY, MATCH . . . – OlimilOops – 2013-12-06T19:54:01.510
see extra answer below – OlimilOops – 2013-12-06T20:22:10.253
1If you find a answer useful, please mark it as accepted answer. You help answerers to get reputation, and other questionaires to find their answer more quickly. – OlimilOops – 2013-12-08T12:43:56.433