# Averageif multiple columns

1. ## Averageif multiple columns

I am trying to average data for multiple locations and multiple days- example below

1st 2nd 3rd 4th
vermont 1.5 1.8 .3 1.2
vermont .6 .8 1.2 .9
vermont etc...
vermont
dallas
dallas
new york
new york
new york

I want to pull an average for all four days for each location. Ideas? When I do =AVERAGEIF(Markets!A:A,Districts!A2,Markets!C2:H13) it is only pulling the average from the first day listed not all of them.

2. ## Re: Averageif multiple columns

Try it like this...

Array entered**:

=AVERAGE(IF(Markets!A2:A13=Districts!A2,Markets!C2:H13))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

With the AVERAGEIF function, the ranges must be the same size.

3. ## Re: Averageif multiple columns

So that works for the first location but I am not sure I am understanding the array portion. It returned the correct number for the first location but every location after it is just pulling 0.00

4. ## Re: Averageif multiple columns

Or it just is pulling #div/0!

5. ## Re: Averageif multiple columns

Did you enter it as an array? If you entered it correctly (using CTRL SHIFT ENTER), when you look at the formula in the formula bar, it will be surrounded with {}

6. ## Re: Averageif multiple columns

Maybe you need something a bit more robust...

Array entered**:

=IFERROR(AVERAGE(IF(Markets!A2:A13=Districts!A2,IF(Markets!C2:H13<>"",Markets!C2:H13))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

7. ## Re: Averageif multiple columns

If that doesn't work for you then I would suggest you post a SMALL sample file so we can see what your data looks like.

A sample file with about 20 rows worth of data is plenty.

8. ## Re: Averageif multiple columns

Alright I didn't understand how the array worked but after messing around for a little bit i got it working correct. Thank you.

9. ## Re: Averageif multiple columns

Good deal. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)