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

user_15144361 0
Viewed 36

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?

google-query-language google-sheets count sum average
1 Answers

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

user_56326290
Related