Need to establish an average across multiple columns where multiple criteria met

[steeler11111]

Hi all, I hope someone can help me with this. In the attached sheet I am trying to establish a team % average (for each team) based on results posted in multiple columns where the date of the result matches the month of the average being sought alongside other criteria met. The criteria required in the attached are as follows:

*

Date is greater than or equal to 01/04/16
Date is less than or equal to 30/04/16
Team name matches the team name in column AH
Grade matches what is in AI7 and AJ7 (results to display below for each team and each grade)

*

The reason for this issue is results are obtained on a 5 weekly basis within my company and until next April cannot be reverted to monthly results. I need to obtain monthly averages however.

*

The best I can do is establish and average of all of this criteria using only one column for results and it's associated date column. I get stuck when I try and tell it I want to check 3 columns results and 3 columns dates to get an average within one formula.

*

in case anyone missed what I'm getting at. Looking at results in columns E, G and I want an average if the date of the result was in April using columns F, H and J plus team name must match and grade to match also.

[BadlySpelledBuoy]

Is this what you mean?

BSB

[steeler11111]

Yes thank you! I was just in the process of trying this with sum as an array when your answer came through. Just one question on this, in the first part of the formula, your sum product specifies to add up the results in $E$3:$I$157 if the dates are within the parameters. Why does it not add up the date values also in $E$3:$I$157 being as dates are also values when formatted as number. Or is it that it won't add them up as the dates are not in number format?

Genius though, lifesaver! Thanks again

[steeler11111]

Yes thank you! I was just in the process of trying this with sum as an array when your answer came through. Just one question on this, in the first part of the formula, your sum product specifies to add up the results in $E$3:$I$157 if the dates are within the parameters. Why does it not add up the date values also in $E$3:$I$157 being as dates are also values when formatted as number. Or is it that it won't add them up as the dates are not in number format?

Genius though, lifesaver! Thanks again

[BadlySpelledBuoy]

Although the dates do fall within the same array they are ignored in the summing process because the ranges are offset by one column.
Look at the arguments that ascertain the date.

Please Login or Register  to view this content.

You'll notice they are offset (F:J as opposed to E:I).

It's not particularly straightforward to explain without lots of diagrams but effectively the formula is saying sum cells in E:I where the value to the right are dates that fall within the specified range.
As the percentage values are not seen by Excel as dates, they're effectively ignored.

I'm sure there are far better explanations out there on SUMPRODUCT. Must be as I'd have to have learned if from somewhere!

BSB

[steeler11111]

No that makes sense, I do try and understand the solutions I get on here or I'll never be able to apply theory to them going forwards. Thanks for taking the time out to explain.

[BadlySpelledBuoy]

Not a problem. Happy to help

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB