averageifs over several ranges

[phstol]

Hello,
I want to get the average of numerical values in 3 ranges , eg A2:A15, A20:A30, A35:A45, ">0". The intervening cells contain other data, so the ranges are not contiguous. I think AVERAGEIFS is the function to use.
I have found my way through nested IF and other functions so am reasonable able to trouble shoot a function . But have not been able to solve this one, would appreciate help
phstol

[NBVC]

I am assuming that the data in between is numeric? Because it it test then you can include them in the range and get correct results...

e.g =AVERAGEIF(A2:A45,"<>0")

Otherwise, try:

=SUM(SUMIF(INDIRECT({"A2:A15","A20:A30","A35:A45"}),"<>0"))/SUM(COUNTIF(INDIRECT({"A2:A15","A20:A30","A35:A45"}),"<>0"))

[phstol]

Thanks so much for yr speedy reply. I couldn't get your solution to work, even after much messing with the format of the string.
But it gave me the clue......
I used SUMIF(A2:A15,">0")+SUMIF(A20:A30,">0")+SUMIF(A35:A45,">0")/COUNTIF(A2:A15,">0")+COUNTIF(A20:A30,">0")+COUNTIF(A35:A45,">0").
THis gives me the average numeric value for only cells with a >0 value.

I found the above works with or without the additional "SUM" as in: SUM(SUMIF(...../SUM(COUNTIF(....

THis solution seems clumsy compared to yurs so if you have any comments on editing yours to make it work I'd appreciate it.

REf the INDIRECT, I had not come across this before. I don't understand from Excel's Help how it is different or better than "anchoring" cell locations using $. Wd appreciate your insight on this.

Anyway, I usually don't anchor the cells while building a complicated spreadsheet because I'm adding /deleting and moving things around , and prefer to let Excel keep track of cells sliding around during development.
Much appreciate your help

[NBVC]

I guess for the three ranges you have, your way is just as good... but my formula allows you to add more ranges with making the formula too large..

The Indirect() method here is used as to trick Excel into getting SUMIF() to evaluate each range independently against the same criteria. Then the SUM() adds the 3 results as your formula did.

My formula also uses criteria <>0 wheras you use >0... might be different based on your data...

Should have worked as yours did...