Trying to make a table to average cells between two dates I specify in a workbook.

I have a workbook that separates each month in a different tab. Within that tab are 11 columns for different stations. Each day of the month is listed down the side and split into 3 shifts. Part counts are recorded in the boxes when parts are ran, sometimes no parts are ran so they enter a 0 or leave blank.

How can i have the "Calc" tab have each cell listed (11) and let me enter two dates in which it will average the part counts for all shifts within those dates i enter??? I have tried a few variations of AVERAGEIF with little to no luck. I am familiar with most basic formulas in excel and how they work, but this one is really testing me....any help would be greatly appreciated!!

Hello and Welcome to the forum what results you are expecting for c100 for your given dates in calc tab

Copy paste below in C4 and drag to right
=AVERAGE(INDEX(JAN!$C$2:$M$94,MATCH(CALC!$A$4,JAN!$A$2:$A$94,0),MATCH(CALC!C$3,JAN!$C$1:$M$1,0)):INDEX(JAN!$C$2:$M$94,MATCH(CALC!$B$4,JAN!$A$2:$A$94,0)+2,MATCH(CALC!C$3,JAN!$C$1:$M$1,0)))

hope this helps

Copy paste below in C4 and drag to right
=AVERAGE(INDEX(JAN!$C$2:$M$94,MATCH(CALC!$A$4,JAN!$A$2:$A$94,0),MATCH(CALC!C$3,JAN!$C$1:$M$1,0)):INDEX(JAN!$C$2:$M$94,MATCH(CALC!$B$4,JAN!$A$2:$A$94,0)+2,MATCH(CALC!C$3,JAN!$C$1:$M$1,0)))

hope this helps

if this is helpful click "*" add rep icon in the bottom left corner of my post

Hello Afazar ! I was quite busy so could not respond . Copy and paste below
=SUM(INDEX(JAN!$C$2:$M$94,MATCH(CALC!$A$4,JAN!$A$2:$A$94,0),MATCH(CALC!C$3,JAN!$C$1:$M$1,0)):INDEX(JAN!$C$2:$M$94,MATCH(CALC!$B$4,JAN!$A$2:$A$94,0)+2,MATCH(CALC!C$3,JAN!$C$1:$M$1,0)))/SUM(IF(INDEX(JAN!$C$2:$M$94,MATCH(CALC!$A$4,JAN!$A$2:$A$94,0),MATCH(CALC!C$3,JAN!$C$1:$M$1,0)):INDEX(JAN!$C$2:$M$94,MATCH(CALC!$B$4,JAN!$A$2:$A$94,0)+2,MATCH(CALC!C$3,JAN!$C$1:$M$1,0))>0,1,0))
then hold control and shift , now hit enter and release all three keys to make it array formula drag to right
and This formula will work for any dates between month but not for workbook.

yes you need to change start date and end date i.e. 10/01/2014 , 15/01/2014 to your desired date !

but dates should be in the same month ! i.e. like 02/02/2014 and 20/02/2014 instead of 02/02/2014 and 20/05/2014 and like wise

Yes ! you are correct

Thanks for the feedback ! Will try with multiple sheets and get back if possible