I'm trying to write a formula that can match 2 criteria and if the match is TRUE then it returns the average of the range provided.

Criteria are:

  • User ID
  • Week number.

So for the user id provided, I want it to match it with the week number and then return the average of that week from the whole range.

I tried below, though I may know what is wrong with it I cannot really find a solution for it.

=IF(and('Stockholm perf'!A5:A32=$A3,'Stockholm perf'!C3:AB3=C$2),AVERAGE('Stockholm perf'!C5:AB32),"WRONG")

  • Am I guessing that column A contains the IDs and columns C to AB contain the values ​​of each of the 26 weeks?
    – Daniele
    Apr 7 at 11:10
  • Ok, so in sheet 1. IDs are in Column A. C3:AB3 have the week numbers, let'say 13. Then the row below are the days. Mon to Sun. For these days if they belong to week 13 then C3:AB3 will contain 13 from Mon to Sun. The data that needs to be averaged is in between C5:AB32. Now in another sheet called "Summary". I would like to match the ID with the week number 13 which indicates Mon to Sun (there are 7 different values for each week + other weeks also), then take the average of that person in that week and return the value.
    – cryptocyp
    Apr 7 at 20:08

1 Answer 1


If your data is in columns A (id), B (week number), and C (the data you want to average), then your formula is:

=query(A2:D,"select avg(C) where A="&G2&" and B="&G3)

where G2 contains the desired ID and G3 contains the desired week number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.