Count & if Across All Sheets

[Cristinky420]

Good day!

Looking for some help here please

I have a workbook, with sheets Metrics, Master, numbered sheets 0001-0076 and Last sheet on the workbook is "Lists". Sheets 0077-1000 will be added at a later date.

I'm looking to pull data from other sheets into my Metrics sheet.

I have a date range on my Metrics sheet - Start Date is B2, End Date is B3.
My numbered sheets have Dates in A5:A5000 and sometimes there is an "x" in the adjacent B cell.

Using my range of numbered sheets (0001:0076), if the date A5:A5000 on that sheet falls within the date range specified in Metrics!B2&B3, I would like it to count the "x" in B5:B5000 in that same row.

Thank-you in advance for your help!

 

[Eric W]

I assumed that the number of the highest sheet is in B5, if so, you can use this array formula:

=SUM(COUNTIFS(INDIRECT(TEXT(ROW(INDIRECT("1:"&B5)),"0000")&"!$A$5:$A$5000"),">="&B2,INDIRECT(TEXT(ROW(INDIRECT("1:"&B5)),"0000")&"!$A$5:$A$5000"),"<="&B3,INDIRECT(TEXT(ROW(INDIRECT("1:"&B5)),"0000")&"!$B$5:$B$5000"),"X"))

Confirm this with Control+Shift+Enter.