Results 1 to 10 of 10

Thread: Need help with lookup/count formula

  1. #1

    Need help with lookup/count formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    Hope someone can help me with a formula. I am looking for a formula to put into sheet 1 that will average column I in sheet2 (averaging only numbers that are 10 or below) that appear between two dates (in sheet1 - month start/end dates, weekly mon/sun dates), I have tried several different types of formulas I have searched for online without any luck.

    I have attached a small sample file of my data.

    Hope someone can help, let me know if I dont make any sense - really sorry in advance this is my first time on these forums
    Attached Files Attached Files

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    What is the difference between 'J Offer Issued' and 'S offer Issued?'

  3. #3
    Quote Originally Posted by JeffreyWeir View Post
    What is the difference between 'J Offer Issued' and 'S offer Issued?'
    Hi Jeffrey,
    S Offer Issued is another set of data I havent entered into my spreadsheet yet, but its basically the same information on Sheet 2, just for another section of the department, the dates may vary. Hope that helps

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Using your date ranges, try in B2:

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")

    copied across

    and in B7:

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H6,Sheet2!$H$2:$H$1142,"<="&H7,Sheet2!$I$2:$I$1142,"<=10"),"")

    copied across


  5. #5
    Quote Originally Posted by NBVC View Post
    Using your date ranges, try in B2:

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")

    copied across

    and in B7:

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H6,Sheet2!$H$2:$H$1142,"<="&H7,Sheet2!$I$2:$I$1142,"<=10"),"")

    copied across
    OMG NBVC I think your the best! I changed the portion of the formula where you had G$2 to H$2, H colum would be the column that has the dates we would be looking at (sent dates) and the numbers showed up! Yeey thank you so much! Really really appreciate it!

    Quick question, the date ranges, is that inclusive or only between those two dates?
    Last edited by TTTNT; 2013-08-20 at 11:24 PM.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    the dates are inclusive. (i.e >= means greater than or equal to and <= means less than or equal to). If you don't want inclusive remove the = signs.


  7. #7
    Quote Originally Posted by NBVC View Post
    the dates are inclusive. (i.e >= means greater than or equal to and <= means less than or equal to). If you don't want inclusive remove the = signs.
    That's right, apologies for the silly question. and thank you so much for the help on the formula!

  8. #8
    Quote Originally Posted by TTTNT View Post
    That's right, apologies for the silly question. and thank you so much for the help on the formula!
    Quick question referring to your formula, is it possible to add a text criteria also? like, average all turnaround time for all the ones that say 'package' (in Sheet2 - Column E) that fall between criteria dates?

    May need to know what the average turnaround time is for all packaged and standard items between those dates (no more than 10 days out)

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Yes, you can

    e.g.

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$E$2:$E$1142,"package",Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")


  10. #10
    Quote Originally Posted by NBVC View Post
    Yes, you can

    e.g.

    =IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$E$2:$E$1142,"package",Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")
    Thank you!!! I think your my excel formula hero :P

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •