Need help with lookup/count formula

TTTNT

New member
Joined
Aug 19, 2013
Messages
6
Reaction score
0
Points
0
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
 

Attachments

  • Sample Data.xlsx
    385.1 KB · Views: 23
What is the difference between 'J Offer Issued' and 'S offer Issued?'
 
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
 
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
 
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:
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.
 
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!
 
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)
 
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"),"")
 
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
 
Back
Top