yogi_From Table Of Dates And Visitors Create A Table Of 'Week Number' 'Unique Visitors' And 'Number Of Visits'

Google Spreadsheet   Post  #2102

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2017

question by: DanielAsk:

https://productforums.google.com/forum/#!topic/docs/buqGkCOUOcA

How to list by week and count unique cells in adjacent column
I have a table with two columns. In the first column I list the dates ("yyyy-mm-dd hh:mm" format) and in the second column I list the names (visitors).

Column 1 Column 2

2016-12-20 16:04 John

2016-12-20 20:02 Mike

2016-12-21 10:36 Mike

2016-12-22 11:15 George

2016-12-24 12:11 George

I would like the output of the formula to be on three columns: Col1-unique number of weeks, Col2-count unique number of visitors during the week, Col3-count number of visits. The diference between Col2 and Col3 is that in the first one a visitor will only be counted once no matter how many visits he had during the week while in Col3 each visit will be counted.

Since 08/15-21 is week 33 and 08/22-28 is week 34, the output should like this:

Week Unique visitors Number of visits

33 2 3

34 1 2

Based on what I've read so far, I manage to use the following function to get unique number of weeks (group by week) and number of visits:

query(index(if({1\0};WEEKNUM(DATA_INFO_3!C2:C);DATA_INFO_3!D2:D));"select Col1,count(Col2) where Col2<>'' group by Col1 label count(Col2) '' ") My problem is that I do not manage to count the unique number of visitors as grouping by both Col1 and Col2 will return an error. Your help would be much appreciated.