+ Reply to Thread
Results 1 to 2 of 2

Finding average between dates that reference a cell for names

  1. 05-15-2019, 07:21 AM #1
    miribilist
    miribilist is offline
    Registered User
    Join Date
    10-27-2011
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Finding average between dates that reference a cell for names

    I have a sheet "data!" set going back 5 years on rows 2-5000.
    Names in column Q and a time in column AK.

    I have second sheet "2018 Analysis" with names in cells K7:K10 that I need to reference against the first sheet.

    I have dates Jan 1st 2018 in cell A1 and Dec 31st 2018 in cell B2.

    I need cells R7:R10 to show me the average time on a column AK in "data!" in the between the dates in cells A1 & A2 on "2018 Analysis" for the name in cells K7:K10.


    I have managed to get this to work when calculating a SUM, but I need an AVERAGEIF.

    The formula in different cells is:
    =SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

    This works and calculates the sum between the cell ranges, references the name (K7) and also only on dates required between cells A2 & A3


    Hope someone can help!
    Reply With QuoteRegister To Reply
  2. 05-15-2019, 07:58 AM #2
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Finding average between dates that reference a cell for names

    Hi

    the following formula should work as a COUNTIF

    =SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

    Adding the segment "*AK2:AK1832"

    =SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3)*AK2:AK1832)

    you should get the sum of numbers (times) in AK2:AK1832 under same conditions


    To get the average

    =SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3)*AK2:AK1832)/SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

    Or to be confirmed with control+shift+enter


    =AVERAGE(IF(data!Q2:Q1832=K7,IF(data!G2:G1832>=A2,IF(data!G2:G1832<=A3,AK2:AK1832))))


    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reference all sheets with dates for names by Month
    By jtmoore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2019, 01:24 PM
  2. Finding Average/Min/Max based on another cell
    By msche09 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2018, 02:38 AM
  3. Finding the average of two dates.
    By sungen99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2015, 09:51 AM
  4. From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And Ther
    By rahuleyes in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 17
    Last Post: 11-27-2014, 03:44 PM
  5. Finding a Single name in Cell in a Cell with Multiple Names (words)
    By perm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-08-2014, 12:29 AM
  6. Finding Proper Names in a Cell of Text
    By hiimdoug in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-05-2013, 04:53 PM
  7. Finding AVERAGE over dates
    By magicrat80 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-14-2011, 01:38 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1