+ Reply to Thread
Results 1 to 7 of 7

AVERAGEIFS Formula with Date Range and Matching a Name

  1. #1
    Registered User
    Join Date
    06-08-2014
    Posts
    6

    AVERAGEIFS Formula with Date Range and Matching a Name

    Hello,

    I am trying to use the AVERAGEIFS (for the first time), I have a date range that changes based upon a person and need the average of values based on dates to match the name.

    I have attached a spreadsheet to gain insight.

    Thanks for any help provided!

    Diane
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    In D3

    =AVERAGE(IF(Sheet2!$A$2:$A$18=A3,IF(Sheet2!$B$1:$AH$1>=B3,IF(Sheet2!$B$1:$AH$1<=C3,IF(Sheet2!$B$2:$AH$18>0,Sheet2!$B$2:$AH$18)))))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Last edited by AlKey; 06-09-2014 at 12:22 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    06-08-2014
    Posts
    6

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    I am getting a 0 as the answer with the array brackets?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    Please see attached below.
    Last edited by AlKey; 06-09-2014 at 12:17 PM.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    Sorry, here is a corrected version.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-08-2014
    Posts
    6

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    Perfect! Thank you!

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: AVERAGEIFS Formula with Date Range and Matching a Name

    You're welcome

    Here is also your formula for the previous request to sum values where some cells have dashes.

    =SUM(IF(ISNUMBER(Sheet2!$B$2:$AA$28),IF(Sheet2!$A$2:$A$28=A3,IF(Sheet2!$B$1:$AA$1>=B3,IF(Sheet2!$B$1:$AA$1<=C3,Sheet2!$B$2:$AA$28)))))


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

+ 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. Sum ifs - Based on Matching Date (exact date not range)
    By alyssakhan in forum Excel General
    Replies: 11
    Last Post: 06-03-2014, 03:35 PM
  2. [SOLVED] Help with array using date range and matching a third constant
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2013, 02:35 PM
  3. Indexing and matching data from date range and matching
    By Rickomicko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2013, 11:46 AM
  4. [SOLVED] match closest date from within range matching ID number
    By adrianjaeggi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2012, 03:33 PM
  5. Count a Matching criteria to a date range
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2010, 02:25 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