Hello all,

I have a workbook which will end up with dozens of worksheets. I am needing to average the numbers of various worksheets based on multiple criteria. I have been successful at doing this with a single criterion using a combination of SUMPRODUCT, SUMIF, COUNTIF & INDIRECT. The formula I use for the single criterion is -

=SUMPRODUCT(SUMIF(INDIRECT("'"&stores&"'!k4:n4"),A20,INDIRECT("'"&stores&"'!n1")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&stores&"'!k4:n4"),A20))


I have tried adapting this formula to a couple of methods mentioned using the double negative and multiplying the different criteria but always get a #VALUE error. This is the latest attempt I have made and would greatly appreciate any suggestions.

=SUMPRODUCT((INDIRECT("'"&stores&"'!K5")>=2)*(INDIRECT("'"&stores&"'!k5")<4)*(INDIRECT("'"&stores&"'!n1")))/SUMPRODUCT((INDIRECT("'"&stores&"'!k5")>=2)*(INDIRECT("'"&stores&"'!k5")<4))


stores is a named list of stores on the primary worksheet. Each store has its own worksheet with the name of the store number. Names in the list are identical to the name on the worksheet.
K5 is the location of number being compared (years of service).
N1 is the location of the number I am trying to sum and count to find the average.