2

I have a small data set that I need to find the average number of times each individual user (email column B) appears within a defined period.

In the sheet link I have the total number of times each user is recorded in column c. I can't just average column c as the users appear many times each. Somehow I need to use Unique email value for the average.

On top of this I need to define a date range using dates in column A. A range of 04/10/2021 - 05/30/2021

To make this more complex, There are missing values in some cells. Where there is a missing value the row should be ignored.

https://docs.google.com/spreadsheets/d/1EMFEvmGeiaTP-E8OsiQSxb0TWG18kYSScpKGpR2SgXk/edit?usp=sharing

Any suggestions how to achieve this?

player0
  • 124,011
  • 12
  • 67
  • 124
Stuart
  • 315
  • 1
  • 8
  • Can the defined period be anything - a day, a week, a month etc.? Maybe give an example of desired result for a particular period so contributors can check their formulas. – Tom Sharpe May 24 '22 at 11:33
  • Hi Tom, sorry the defined period can be any of the dates for now. A range of 04/10/2021 - 05/30/2021 – Stuart May 24 '22 at 12:54

1 Answers1

3

try:

=QUERY(QUERY(A2:C, 
 "select B,count(B) where (A is not null or B is not null) group by B"), 
 "where Col1 is not null", )

enter image description here

update:

=QUERY(QUERY(A2:C, 
 "select B,count(B) 
  where (A is not null or B is not null) 
    and A >= date '2021-04-10'
    and A <= date '2021-05-30'
  group by B"), 
 "where Col1 is not null", )

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you. This works great where Column A dates are sorted in order and specified within the 'QUERY(A2:C,' part. I'm not sure how to then apply a specific date range within the query such as >=04/10/2021, <=05/30/2021. any pointers please? – Stuart May 24 '22 at 14:30
  • @Stuart answer updated – player0 May 24 '22 at 16:19
  • 1
    Not entirely sure how this is working but it does in both test and live data, thank you – Stuart May 25 '22 at 19:39