+ Reply to Thread
Results 1 to 6 of 6

AVERAGEIF with name and between dates

  1. #1
    Registered User
    Join Date
    01-23-2021
    Location
    Missouri
    MS-Off Ver
    2016
    Posts
    3

    AVERAGEIF with name and between dates

    I'm using average IF the name located in B1 matches on Sheet 1 Row K
    Then the scores to average are on Sheet 1 Row P
    I get that to work just fine with
    =IF(D6=0,"",AVERAGEIF(`Sheet 1`!K:K,B1,`Sheet 1`!P:P))

    However, now I need to tell it to also only give me the results if it is between 2 dates located on H1 & J1.

    B1 is a name that can change
    H1 & J1 is a date that can change (start date, end date)
    D6 is a number that pulls from Sheet 1. It looks for the name in B1 and counts the times it shows up.
    Sheet 1 has all the data
    Sheet 1 Row K is where the names are located
    Sheet 1 Row P is where the scores are located

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,872

    Re: AVERAGEIF with name and between dates

    Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,002

    Re: AVERAGEIF with name and between dates

    A guess, as you didn't tell us where the dates are stored:

    =IF(D6=0,"",AVERAGEIFS('Sheet 1'!P:P,'Sheet 1'!K:K,B1,'Sheet 1'!M:M,">="&H1,'Sheet 1'!M:M,"<="&J1))

    NEXT TIME, add a sample sheet right from the start.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    01-23-2021
    Location
    Missouri
    MS-Off Ver
    2016
    Posts
    3

    Re: AVERAGEIF with name and between dates

    Sorry, new to this forum, usually I figure it out without having to post questions. I see that now and will remember it in the future. Not everyday I use forums that require attachments.
    Sheet 1 row C is where the dates are stored.
    Thanks for the help, I was trying IF(AND...) but couldn't figure it out.
    This worked. Thanks for the quick response. Again, I'll remember to post a sample workbook of what I'm doing next time.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,872

    Re: AVERAGEIF with name and between dates

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  6. #6
    Registered User
    Join Date
    01-23-2021
    Location
    Missouri
    MS-Off Ver
    2016
    Posts
    3

    Re: AVERAGEIF with name and between dates

    Thanks, didn't know about that. Showed it solved now and clicked the star button.

+ 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 to give result as % from range of dates
    By Elliott_the_noob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2019, 12:33 PM
  2. [SOLVED] Averageif
    By bangorbrownie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2017, 07:01 PM
  3. [SOLVED] help with =AVERAGEIF
    By Joshua Buche in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2014, 06:36 PM
  4. [SOLVED] AverageIF
    By Brennen81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2014, 03:31 PM
  5. [SOLVED] Averageif for dates (variable year)
    By sherry_1991 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-25-2013, 02:17 PM
  6. Excel 2003 Averageif using dates
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2012, 05:56 AM
  7. AverageIf
    By Guikey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2008, 05:40 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