Average numbers from multiple columns based on multiple criteria

Hi,

I have a large spreadsheet with 2500+ rows and many columns. I am trying find a formula that will average values from about 4-5 columns, based on values in two other columns. My attempt is below, but it doesn't seem to be working in all scenarios, so I don't think it's correct:

Please Login or Register  to view this content.

Sheet1 is the large spreadsheet
Participant Engagment is the sheet where I'm trying to calculate the summaries
Participant Engagement B32 has a text value that I want was one criteria for the average
I want to average non-blank values in four columns, based on the corresponding text value ("Intervention) in the previous column

Thanks in advance!

Here's a very basic, cleaned up version of what I'm trying to do.

I want the calculation on Sheet1, based on data in Sheet 2.
Criteria A, is based on row 1 in Sheet 1, and I want the Average of all the average ranges combined, for values that have "I" in the respective CriteriaB range. Hope that makes sense.

In theory, all the I's should have non-zero numbers, so a forumla that doesn't use Criteria B, but ignores zeroes and blanks might work. However, the former is preferred.

AverageMultColumn&Criteria.xlsx

This seems close, but didn't give me the correct value when I filtered and averaged. However, I don't understand the formula enough, particularly the ranges listed, to troubleshoot...

Sorry, I'm new to this forum. This was in resposne to bebo02199..

Originally Posted by abambi

This seems close, but didn't give me the correct value when I filtered and averaged. However, I don't understand the formula enough, particularly the ranges listed, to troubleshoot...