Adding up averages after checking another column

[Cmorgan]

Evening,

I am using this formula:

=AVERAGE('Input Sheet'!H:H)

To work out the average value in column H.

However, I now need to be more selective:

- I only want to work out the average of the cells in H, if the value in column E is the same value as in A8, A9, A10, A11 or A12.

Thanks for any help you can give.

[SMILE]

Try this
=IF(E1=A8+A9+A10+A11+A12,AVERAGE('Input Sheet'!H:H),"")

Hope it helps
Toms

[Andrew-R]

I don't think that's quite what they're looking for - that will still average all of the rows in column H, but will only show the result if the first value in column E is equal to the sum of A8:A12.

I'm sure there must be an easier way to do this, but the first formula I came up with (set up to work on the first ten rows of columns E & H) is =SUMPRODUCT(H1:H10,--NOT(ISERROR(MATCH(E1:E10,$A$8:$A$12,0))))/SUMPRODUCT(--(NOT(ISERROR(MATCH(E1:E10,$A$8:$A$12,0)))))

[daddylonglegs]

Hello Andrew,

That works for me but it's probably better to use ISNUMBER rather than NOT(ISERROR.....if you don't mind using an "array formula" this is also an option

=AVERAGE(IF(COUNTIF(A8:A12,E1:E10),H1:H10))

confirmed with CTRL+SHIFT+ENTER

[Andrew-R]

I don't mind using an array formula, but then it's not my problem

Personally I'm pretty new to array formula, so I wasn't entirely sure how they'd work when you had one range that was static (A8:A12) and another that was being looped through (E1:E10)

I suppose that at the very least I should have used ISNA rather than ISERROR in my formula.