# Thread: Need help with lookup/count formula

1. ## Need help with lookup/count formula

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

2. What is the difference between 'J Offer Issued' and 'S offer Issued?'

3. Originally Posted by JeffreyWeir
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. 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. Originally Posted by NBVC
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?

6. 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. Originally Posted by NBVC
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. Originally Posted by TTTNT
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. 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. Originally Posted by NBVC
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
•