Average If - averaging cells based on value of another cell

[Jarod]

I have a spreadsheet where O:62 and O:63 are products of a formula in their
rows. The cells in between are segmented, so I can't do a simple O1:0:60,
it's more like O5:O9,O16:O21,etc

So, if C5:C60=C62, I need the average of O5:O9, O16:O21, O28:O33, O41:O45, &
O52:O57. I can't just do O5:O57 because in between the sections are other
averages.

Thanks in advance!

 

[Mike H]

Jarod,

I'm confused about the criteria so what this does is if the sum of C5:C60
=C62 then it averages your non contiguous range.

=IF(SUM(C5:C60)=C62,AVERAGE(IF(ISNUMBER(MATCH(ROW(O1:O57),{5,6,7,8,9,16,17,18,19,20,21,28,29,30,31,32,33,41,42,43,44,45,52,53,54,55,56,57},0)),O1:O57)),"")


It's an array so commit with Ctrl+Shift+Enter NOT just enter. If you've done
it correctly then Excel will put curly brackets around it {}. You cannot type
these yourself.

Mike

 

[Pete_UK]

Sounds like you could do with a re-design !!

Nevertheless, try this array* formula:

=AVERAGE(IF(C5:C60=$C$62,O5:O60))

*An array formula must be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter>. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - you must not type these yourself. Use CSE again if you need to
amend the formula in future.

Hope this helps.

Pete

 

[Jarod]

Sorry, I just meant that the range C5:C60 has the criteria, not that it's a
sum.

 

[Jarod]

I agree, it does need a redesign. Unfortunately, I'm in no position to
approve/undertake it. This formula is what I needed, I won't have criteria
in the "subtotal" row, so it wouldn't count it anyways. Don't know why I
didn't see that.

Thanks!

 

[Pete_UK]

You're welcome, Jarod - thanks for feeding back.

Pete