1

I am trying to find a formula that will give me the count of unique dates a persons' name appears in one of two different columns and/or both columns.

I have a set of data where a person's name may show up in a "driver" column or a "helper" column, multiple times over the course of one day. Throughout the day some drivers might also be helpers and some days a driver may come in for duty but only as a helper. Basically all drivers can be helpers, but not all helpers can be drivers.

I've attached a link to a sample sheet for more clarity. https://docs.google.com/spreadsheets/d/1GqNa1hrViX4B6mkL3wWcqEsy87gmdw77DhkhIaswLyI/edit?usp=sharing

I've created a REPORTS tab with a SORT(UNIQUE(FLATTEN)) Formula to give me a list of the names that appear in the DATA Tab.

I'm looking for a way to count the unique dates a name from the name (Column A of the REPORTS Tab) appears in either of the two columns (Column B and/or C of the DATA Tab) to determine the total number of days worked so I can calculate the total number of days off over the range queried.

I've tried several iterations of countif, countunique, and countuniqueifs but cannot seem to find a way to return the correct values.

Any advice on how to make this work would be appreciated.

Shaun Christian
  • 35
  • 3

2 Answers2

0

I think if you put this formula in cell b7 you'll be set. You can drag it down.

=Counta(Unique(filter(DATA!A:A,(DATA!C:C=A7)+(DATA!B:B=A7))))

Here's a working version of your file.

For anyone interested, Google Sheets' Filter function differs slightly from Excel's Filter function because Sheets attempts to make it easier for users to apply multiple conditions by simply separating each parameter with a comma. Example: =filter(A:A,A:A<>"",B:B<>"bad result") will provide different results between the Sheets and Excel.

Excel Filter requires users to specify multiple conditions within parenthesis and denote each criterion be flagged with an OR condition with a + else an AND condition with a multiplication sign *. While this can appear daunting and bizarre to multiply arrays that have text in it, it allows for more flexibility.

To Google's credit, if one follows the required Excel Syntax (as I did in this answer) then the functions will behave the same.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
0

delete what you got and use:

=QUERY(QUERY(UNIQUE({DATA!A:B; DATA!A:A, DATA!C:C}), 
 "select Col2,count(Col1),"&D2&"-count(Col2)
  where Col2 is not null 
  group by Col2"),
 "offset 1", 0)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124