AVERAGEIF for multiple ranges

**soccow** · 09-17-2012, 02:14 PM

I am looking to use averageif for multiple ranges:

=AVERAGEIF((A120:B124,A182:B186,A244:B248,A306:B310,A368:B372,A430:B434,A492:B496,A554:B558,A616:B620,A678:B682), "Individual Training", (K120:K124,K182:K186,K244:K248,K306:K310,K368:K372,K430:K434,K492:K496,K554:K558,K616:K620,K678:K682))

but I keep getting #VALUE! returned.

Am I inputting incorrectly, or is there another way to do this?

Thanks

**daddylonglegs** · 09-17-2012, 02:26 PM

AVERAGEIF will only let you use single ranges so you'll need to find another way to do that.

Why is the first range two columns (A and B), could both A120 and B120 be "Individual Training"? If so then does that mean you need to include K120 in the average twice?

**FDibbins** · 09-17-2012, 02:41 PM

i know this is a bit long-winded, but have you tried to use =sumif(all those ranges,criteria)/countif(all those ranges,criteria)?

**soccow** · 09-17-2012, 03:07 PM

Originally Posted by daddylonglegs

AVERAGEIF will only let you use single ranges so you'll need to find another way to do that.

Why is the first range two columns (A and B), could both A120 and B120 be "Individual Training"? If so then does that mean you need to include K120 in the average twice?

It is because A and B are merged columns in this area...originally I thought that it might be because of the merging, but when I use just one of the ranges it works, for example: =AVERAGEIF((A120:B124), "Individual Training", (K120:K124))

**soccow** · 09-17-2012, 03:12 PM

Originally Posted by FDibbins

i know this is a bit long-winded, but have you tried to use =sumif(all those ranges,criteria)/countif(all those ranges,criteria)?

Yeah, but I was trying to avoid this, given its length and that I need to use it multiple times with differing ranges and criteria. Also, I am stubborn...I really feel that it should work with averageif. I will resort to sumif/countif if need be. Thanks though!

**soccow** · 09-17-2012, 03:13 PM

Missed the part about only working with single ranges. Ok, so is sumif/countif my only option then?

**daddylonglegs** · 09-17-2012, 03:20 PM

OK, but if you merge A and B the cells will actually be identified as column A normally.

Do you have anything in the specific rows that you want to average that you could use to identify them? E.g. if in column Z you have an "x" in every row for that average, i.e. rows 120, 121, 122, 123, 124, 182, 183, 184, 185, 186, 244 etc. then you could use AVERAGEIFS

=AVERAGEIFS(K120:K682,Z120:Z682,"x",A120:A682,"Individual Training")

**soccow** · 09-17-2012, 06:58 PM

When I select the merged cells, they come up as A120:B124. Not all cells in columns A and B are merged, just the ones that are in my range.

As for using x...I am creating a form for others to fill in, thus I don't know which rows will be designated "individual training" as of yet (they have the option of 4 different pull downs). I want to be able to average the ones that they designate as individual training so I can't predetermine what rows need an "x"