+ Reply to Thread
Results 1 to 9 of 9

Averageif Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Averageif Multiple Criteria

    I'm looking for a formula that will change which cells it averages based upon another cell's data.

    For example:

    In tab "Match-Up1" you'll see the formulas in P3:AJ4. They are dependent on the data in A3:B4. This formula returns the average data for Auburn's games in which they played an SEC opponent. I'd like a formula that will return the results listed in "Match-Up2" if I delete the data in B3 "SEC". That would return the average data for ALL of Auburn's games.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Averageif Multiple Criteria

    perhaps simplest to just treat B as a partial match by appending an * to the criteria value, thus when blank it will find all rows (with text value in I, no numbers stored in this column)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the above will replicate your matchup2 results when SEC is removed.

  3. #3
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Averageif Multiple Criteria

    That's amazing, Xlent! I don't know if you can teach me how you did that, but if you can that'd be amazing. If not would you be willing to help me accomplish one more thing?

    1) Expand the formula to include the variables for C3:H4?
    Match-Up1 column C is looking for a value >7 in "Florida/Auburn" column S. Variables are "Yes" and "No".
    Match-Up1 column D is looking for a value >0 in "Florida/Auburn" column T. Variables are "Yes" and "No".
    Match-Up1 column E is looking for the value "11:00am" to "6:59pm" (Day) or "7:00pm" to "11:59pm" (Night) in "Florida/Auburn" column D. Variables are "Day" and "Night".
    Match-Up1 column F is looking for the value "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" in "Florida/Auburn" column E. Variables are "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat".
    Match-Up1 column G is looking for the value "Yes" or "No" in "Florida/Auburn" column R. Variables are "Yes" and "No".
    Match-Up1 column H is looking for the value " " (blank space), "@", and "N" in "Florida/Auburn" column G. Variables are " " (blank space), "@" and "N".

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Averageif Multiple Criteria

    You can switch to AVERAGEIFS, i.e. existing (single condition) formula would change such that the average range is now listed first.

    The below accommodates your various criterion and, with exception of Venue, will handle blank criterion as "all".

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Notes:

    For "Venue" I would suggest doing following: leave criteria blank, rather than space, to handle "home" -- for "all" enter * in criteria (rather than blank [=home])
    rationale: 'home' fixtures on college tabs are blanks as opposed to spaces.

    To negate need for double test on 'days between games' enter 0 in S3 on each college tab
    rationale: to handle "all" devoid of these 0s you would effectively need to test both text & numbers (double the testing)

  5. #5
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Averageif Multiple Criteria

    Wow, Xlent! This is all very interesting. Thanks a million. Oh, I forgot something.... two FINAL questions!

    1) How can I extend the formula to include cells M3:N4? The exact same principle as P3:AJ4, average the value of column K and L in the team tabs to correspond to the variables in A3:H4?

    2) How can I change the formula to make it a countif formula for cells J3:L4?

    J3 & J4 in "Match-Up", given the variables in A3:H3 & A4:H4 respectively, count the number of times that the criteria in A3:H3 and A4:H4 is met.
    K3 & K4 in "Match-Up", given the variables in A3:H3 & A4:H4 respectively, count the number of times "W" appears in column J when the criteria in A3:H3 and A4:H4 is met.
    L3 & L4 in "Match-Up" give the the variables in A3:H3 & A4:H4 respectively, count the number of times "L" appears in the column J when the criteria in A3:H3 and A4:H4 is met.

  6. #6
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Averageif Multiple Criteria

    I wish I could reverse engineer what you did but I'm having trouble doing that.

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Averageif Multiple Criteria

    refer attached but, in short, use COUNTIFS rather than AVERAGEIFS for your P/W/L.

    note: given additional AVERAGEIFS requirement, and position on college tabs, I have added an index # header {row1} to replace the 25+COLUMNS for use re: R1C1
    (you could determine these values systematically, however given a one-off (and duplicate headers) it's as easy to add as constant values)

    IMO, techonthenet is a good reference for familiarising yourself with the various functions XL has to offer - like COUNTIFS, AVERAGEIFS etc
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Averageif Multiple Criteria

    XLent, that's awesome, but when I change a value in A3:H4 in order for the values in J3:AJ4 to change I have to click on the cell and click on the formula text. I have to do that for every single cell each time I change the variable. Is there a way around this?

  9. #9
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Averageif Multiple Criteria

    Nevermind, I changed it from manual to automatic.

+ 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 with multiple criteria
    By d7882 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2019, 04:31 PM
  2. [SOLVED] AverageIF multiple criteria
    By tsakta13ole in forum Excel General
    Replies: 4
    Last Post: 03-12-2018, 11:41 AM
  3. Replies: 10
    Last Post: 04-16-2016, 09:26 AM
  4. Averageif criteria
    By wpryan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2015, 07:11 AM
  5. Multiple Criteria AverageIF
    By orev2 in forum Excel General
    Replies: 1
    Last Post: 08-19-2014, 01:18 PM
  6. [SOLVED] AverageIF using multiple criteria in different columns in multiple worksheets
    By trubertiam in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-03-2014, 09:28 PM
  7. averageif formula with multiple criteria
    By CPitta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-08-2013, 12:56 PM

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