Range as Criteria in Average Ifs

[strother1990]

Hi

I have a large data set that goes horizontally with criteria above each row like name, group and generation.

Then I have average columns for each generation. I want to use averageifs to average selected groups at a time just by changing the input criteria as shown below.

Is it not possible to have a range as my criteria for averageifs?

....................................................................................................................................................................................................................

[FDibbins]

What exactly are you trying to calc here?

average what, if what = what and what =what?

I dont get how the "table" in I2:L4 come in to this?

[strother1990]

So the I2:L4 are the identifiers for that well. Then below it are the production values for each day.

I want to be able to use the average if function to say for day 1 average all the wells in generation 1 or 2 or 4, that also are in location 45 or 22.
Something like that, does that make sense?

So the table you are asking about is just the information that goes with that production stream. I want to use the inputs on the left side to grab only the ones I want. I am just not sure if average if can use a range of values as a criteria, almost like an or statement (location 35,22,45).

[FDibbins]

OK, try this....
=SUMPRODUCT(($I$6:$L$23)*--($I$2:$L$2=B7)*--($I$3:$L$3=C7)*--($I$4:$L$4=D7))/COUNT($I$6:$I$23)

However, If I understand you correctly, none of the criteria *sets* that you have in B7D12 match the variables in I2:L4. So maybe play around with the values and see if this is what you want?

[strother1990]

Thank you for your help, I tried that and didn't quite work. I dont think I was being clear enough, so I attached another spreadsheet to explain more clearly.

Thank you so much for your time!

Averageifs.xlsx

[canapone]

Hi,

the formula

Please Login or Register  to view this content.

array entered could do the trick.

Regards

[strother1990]

Awesome! Worked!!

[canapone]

Hi, you're welcome.
Glad to help you.