+ Reply to Thread
Results 1 to 4 of 4

Averageif with multiple criteria

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Microsoft 2016
    Posts
    74

    Averageif with multiple criteria

    Hey guys, hoping you could help me out.

    I'm trying to create a formula that would take the average of a column based on 3 criteria. I've attached my file and my poor attempt at it but looks like I need outside help.

    For example, want to find the average time worked based on criteria; Jan 2017 (date), Office Associate (Title), and Active or Fired (Status).

    Thank you for your help everyone!
    Attached Files Attached Files

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

    Re: Averageif with multiple criteria

    J3=IFERROR(AVERAGEIFS($E$2:$E$297,$B$2:$B$297,$H3,$C$2:$C$297,$I3,$A$2:$A$297,J$2),"")

    copy across and down

  3. #3
    Registered User
    Join Date
    08-18-2017
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Microsoft 2016
    Posts
    74

    Re: Averageif with multiple criteria

    Opps, looks like it works!

    Thank you!
    Last edited by d7882; 09-17-2019 at 04:20 PM.

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Averageif with multiple criteria

    Hi -

    You want to use the AVERAGEIFS() formula for multiple criteria. Your formula would look like this:

    =IFERROR(AVERAGEIFS($E$2:$E$297,$A$2:$A$297,J$2,$B$2:$B$297,$H3,$C$2:$C$297,$I3),"N/A")

    Note some of the combinations of criteria have no data, so they end up returning a DIV/0! error. To get rid of that, I enclosed the formula in an IFERROR()

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

+ 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 Formula with 2 criteria
    By lostinaformula in forum Excel General
    Replies: 3
    Last Post: 03-14-2018, 05:57 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

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