+ Reply to Thread
Results 1 to 6 of 6

Nesting multiple AVERAGEIFS

  1. #1
    Forum Contributor
    Join Date
    11-19-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    137

    Nesting multiple AVERAGEIFS

    I have 5 AVERAGEIFS formuals, one for each employee that work fine. I have trouble nesting the 5 employees into one formula. When I add the additional employees to the formula I get a result of #DIV/0!.

    The 5 Average
    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,"Employee1"),"")
    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,"Employee2"),"")
    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,"Employee3"),"")
    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,"Employee4"),"")
    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,"Employee5"),"")

    Thanks for any help.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Nesting multiple AVERAGEIFS

    That's because the range R:R doesn't contain all five names in the same cell simultaneously, resulting in SUM(0)/COUNT(0) error.

    Instead use SUMPRODUCT to apply OR logic.

    Please Login or Register  to view this content.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Nesting multiple AVERAGEIFS

    If you want the average of all 5, just remove the criteria for the person...

    =IFERROR(AVERAGEIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer"),"")

  4. #4
    Forum Contributor
    Join Date
    11-19-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    137

    Re: Nesting multiple AVERAGEIFS

    There are other employees in my spreadsheet. I want the total average of these 5.

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Nesting multiple AVERAGEIFS

    Have you considered my formula? SUMPRODUCT is just a longer form of COUNTIF, SUMIF, and AVERAGEIF, but it can do all of those functions in a more verbose form.
    The problem is that the user-friendly functions that MS made to encourage the use of array expressions just don't nest expressions within them very well.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Nesting multiple AVERAGEIFS

    You can do

    =SUM(SUMIFS('Jan2015'!$Z:$Z,'Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,{"Employee1","Employee2","Employee3"}))/SUM(COUNTIFS('Jan2015'!$T:$T,"Transfer",'Jan2015'!$R:$R,{"Employee1","Employee2","Employee3"}))

    I only did 3 employees, but you should get the idea..
    Average is Sum / COUNT
    So it's basically SUMIFS/COUNTIFS

+ 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] AVERAGEIFS with multiple criteria
    By bibu in forum Excel General
    Replies: 5
    Last Post: 03-22-2014, 03:28 PM
  2. AVERAGEIFS(), with multiple criteria
    By Jkember in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2014, 06:33 PM
  3. AVERAGEIFS Multiple Criteria
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 03:58 PM
  4. [SOLVED] Averageifs with multiple criteria
    By jbillyo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2013, 04:13 PM
  5. [SOLVED] AVERAGEIFS using multiple ranges and multiple criteria
    By LindsayS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 01:49 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