+ Reply to Thread
Results 1 to 9 of 9

Averageif multiple columns

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    11-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #4
    Registered User
    Join Date
    11-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Averageif multiple columns

    Or it just is pulling #div/0!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 {}
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #8
    Registered User
    Join Date
    11-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif multiple columns

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] AVERAGEIF Multiple Columns
    By Markvx in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-29-2015, 08:48 AM
  2. AverageIf of two columns
    By dt32 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2013, 10:47 AM
  3. [SOLVED] Excel 2010 AVERAGEIF function to average two different columns on two different tabs
    By stevemills04 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2013, 01:48 PM
  4. AVERAGEIF (Multiple Worksheets)
    By graybush in forum Excel General
    Replies: 1
    Last Post: 11-02-2011, 09:59 PM
  5. AverageIf based on another columns criteria
    By AllenMead in forum Excel General
    Replies: 7
    Last Post: 09-06-2010, 12:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1