Home > OS >  Average use based on number of times an email address appears within a period
Average use based on number of times an email address appears within a period

Time:05-26

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.

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

  • Related