Irregular AverageIf Formula

[Moe]

I know there's a way to do an averageif array formula. But what about if the cells I'm concerned with aren't adjacent to each other:

In other words: I want to average all my subtotals in a column if they are not 0:

A1 1
A2 2
A3 2
A4 5 (subtotal)
A5 3
A6 3
A7 6 (subtotal)
A8 3
A9 3
A10 2
A11 8 (subtotal)
A12 0
A13 0
A14 0
A15 0 (subtotal)
A16 2
A17 2
A18 4 (subtotal)

I cannot sort this row, or adjust its formatting in any way. I want a formula that would result in 23/4 = 5.75 (23 is 5+6+8+4) and (4 is the # of subtotals >0)

I was thinking something along the lines of:
{average(if(A4,A7,A11,A15,A18<>0,A4,A7,A11,A15,A18,False))}, but I don't think that works

Thanks for your help........
Moe

[Domenic]

Here are a few options for you to consider...

=SUBTOTAL(9,A1:A18)/SUMPRODUCT(--(A1:A18>0),--ISNUMBER(MATCH(ROW(A1:A18),{4,7,11,15,18},0)))

OR

=SUBTOTAL(9,A1:A18)/SUMPRODUCT(COUNTIF(INDIRECT({"A4","A7","A11","A15","A18"}),">0"))

OR

=SUBTOTAL(9,A1:A18)/SUMPRODUCT(--(CHOOSE({1,2,3,4,5},A4,A7,A11,A15,A18)>0))

Hope this helps!

[Domenic]

For my formulas to work, the SUBTOTAL function would have to be used in A4, A7, A11, etc., to get your subtotals. Since that probably isn't the case, you could replace the first formula I offered with the following...

=SUMPRODUCT(A1:A18,--ISNUMBER(MATCH(ROW(A1:A18),{4,7,11,15,18},0)))/SUMPRODUCT(--(A1:A18>0),--ISNUMBER(MATCH(ROW(A1:A18),{4,7,11,15,18},0)))

OR

=SUMPRODUCT(A1:A18,--ISNUMBER(MATCH(ROW(A1:A18),B1:B5,0)))/SUMPRODUCT(--(A1:A18>0),--ISNUMBER(MATCH(ROW(A1:A18),B1:B5,0)))

...where B1:B5 contains a list of rows to average, such as...

4
7
11
15
18

However, if each subtotal cell is labelled with the word 'Subtotal' in the column next to it, let's say Column B, try one of the following formulas instead...

If the word 'Subtotal' is the only word that appears in the cell:

=AVERAGE(IF((A1:A18>0)*(B1:B18="Subtotal"),A1:A18))

If the word 'Subtotal' is the first word that appears in the cell:

=AVERAGE(IF((A1:A18>0)*(LEFT(B1:B18,8)="Subtotal"),A1:A18))

If the word 'Subtotal' can appear anywhere in the cell within the text string:

=AVERAGE(IF((A1:A18>0)*(ISNUMBER(SEARCH("Subtotal",B1:B18))),A1:A18))

These formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!