Saturday, January 21, 2012

yogi_Count Unique Values With Multiple Conditions


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user lambchop said:
Hi, I am new to Google Docs and I'm trying to count unique entries for multiple conditions.  A simple example I have created is to count how many cities each employee is booked to visit and how many cities they actually attend.  Each city may only be counted once for each employee.  See example below,
EMPLOYEE  CITY         BOOKED VISITED
JAMES       SYDNEY           Y Y
JAMES       PERTH            Y N
JAMES       SYDNEY           Y Y
JAMES       BRISBANE         Y Y
SALLY       PERTH            Y Y
SALLY       PERTH            Y N
SALLY       DARWIN           Y Y
ROGER       CANBERRA         Y Y
ROGER       ADELAIDE         Y N
In this case, the results would be
EMPLOYEE BOOKED  ATTENDED
JAMES      3        2
SALLY      2        2
ROGER      2        1
I've created a test spreadsheet at the following link.  The results table above is located in sheet 2 named "RESULTS"
I hope this is the right way to share this file.
-------------------------------------------------------
following is a solution to the problem