+ Reply to Thread
Results 1 to 4 of 4

averageif with multiple criteria

  1. #1
    Registered User
    Join Date
    04-21-2018
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    24

    averageif with multiple criteria

    Good Evening,

    Hope all is well!

    i have 2 tables:

    1st table is for 1 product code: I have hit a brick wall. For example I am trying to get an average age of a product code that is 60 months or under and that has a "YES" in the warranty column in the year 2013.

    My formula is
    Please Login or Register  to view this content.
    i have another 3 cells that i want an average of

    1. The above
    2. The average of the product code for all years for number 1.
    3. The average of the product code regardless of age and if in warranty
    4. the average of the product for all years that is under 60 months and yes in warranty

    2nd table: Is for all codes and same as 1 to 4 above.

    Any help will be most welcome and appreciated.

    I have attached a spreadsheet as an example which i hope explains my wishes.

    Thank you for taking your time out and taking a look.
    Attached Files Attached Files
    Last edited by Rob1970; 11-28-2019 at 05:00 PM.

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: averageif with multiple criteria

    No file attached

    Try again

  3. #3
    Registered User
    Join Date
    04-21-2018
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    24

    Re: averageif with multiple criteria

    Hi CARACALLA,

    Thank you for highlighting this. File attached.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: averageif with multiple criteria

    I would make two changes, one change this "<=60" to this "<="&60 and change the iferror value from "0.00" to 0 and format as two decimal.
    the first change won't make a difference to the outputs (it is more of a syntax issue for me) but the second will, it will change your average in cell AE14.
    =IFERROR(AVERAGEIFS(Table1[Product Age Months],Table1[Year],AA2,Table1[Product Code],$AC$1,Table1[Warranty],"Yes",Table1[Product Age Months],"<="&60),0)

    EDIT: unless you are wanting the zeros to be text to exclude from your result.
    then please indicate what the issue is.
    Last edited by Sam Capricci; 11-28-2019 at 10:55 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

+ 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. Averageif Multiple Criteria
    By quibilty in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2019, 04:46 PM
  2. [SOLVED] Averageif with multiple criteria
    By d7882 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2019, 04:31 PM
  3. [SOLVED] AverageIF multiple criteria
    By tsakta13ole in forum Excel General
    Replies: 4
    Last Post: 03-12-2018, 11:41 AM
  4. Replies: 10
    Last Post: 04-16-2016, 09:26 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