Share via

How can I use AVERAGEIF function with 2 different ranges

Anonymous
2024-11-25T14:57:29+00:00

Hello All,

I have the below table as an example and I want to average what's in column D based on my selection separately.

For Instance, I want to get the average of A1, B6 and C11 all in one formula.

a1 10
a2 20
a3 30
a4 40
a5 50
a6 b6 60
b7 70
b8 C8 80
b9 C9 90
b10 C10 100
b11 C11 110
b12 C12 120
C13 130
C14 140
C15 150
Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-11-25T18:56:53+00:00

    Did you tried my formulas?

    Did you tried to change the content of F1:F3?

    Which result do you calculate for your example a1,b6 and c11?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-11-25T16:36:08+00:00

    Thank you very much, but this is not what I am looking for.

    I am looking for a formula, to get me the average whenever I change the selection, so for instance whenever I select a1 it turns 10, b6 60...etc...

    Was this answer helpful?

    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2024-11-25T16:19:56+00:00

    G1: =IFERROR(INDEX(D:D,MATCH(F1,A:A,0)),"")
    G2: =IFERROR(INDEX(D:D,MATCH(F2,B:B,0)),"")
    G3: =IFERROR(INDEX(D:D,MATCH(F3,C:C,0)),"")
    G4: =AVERAGE(G1:G3)

    Combined:
    =AVERAGE(
    IFERROR(INDEX(D:D,MATCH(F1,A:A,0)),""),
    IFERROR(INDEX(D:D,MATCH(F2,B:B,0)),""),
    IFERROR(INDEX(D:D,MATCH(F3,C:C,0)),""))

    Was this answer helpful?

    0 comments