Counting Date range across the whole workbook

[PCloadletter]

Hello,

I'm trying to figure out the formula for counting certain date ranges across the entire workbook. I was able to figure out the formula to do it from ONE sheet :

=SUMPRODUCT(COUNTIFS(SHEET1!Q:Q,">="&SUMMARY!C16,SHEET1!Q:Q,"<="&SUMMARY!C17))

Now I want to do it across all sheets, so I created a new sheet named "Sites" and listed all the sites from A1:A25. On my summary sheet for C16 to C17, I have the date range Jan 1st 2020 to Jun 30th, 2020.

=SUMPRODUCT(COUNTIFS(INDIRECT('Sites'!A1:A25,">="&SUMMARY!C16,'SITES'!A1:A25,"<="&SUMMARY!C17))

I thought this would work as it did for counting non-date values but I'm getting a "Too many Arguments" error.

Q:Q above would have date ranges on all my sheets.

Any help would be greatly appreciated.

Thank you in advance.

 

[yky]

The formula works for me. I have the same date range in both Sheet1 and Sheet2.

Book2
	A	B	C
1	Sheet1!Q1:Q7		10
2	Sheet2!Q1:Q7		4
3			12
4			8
Sites

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(COUNTIFS(INDIRECT(Sites!A1:A2),">="&Summary!C16))
C2	C2	=SUMPRODUCT(COUNTIFS(INDIRECT(Sites!A1:A2),"<="&Summary!C16))
C3	C3	=SUMPRODUCT(COUNTIFS(INDIRECT(Sites!A1:A2),"<="&Summary!C17))
C4	C4	=SUMPRODUCT(COUNTIFS(INDIRECT(Sites!A1:A2),">="&Summary!C16,INDIRECT(Sites!A1:A2),"<="&Summary!C17))

 

[PCloadletter]

Thank you! I added the range for my sheets and the second indirect command and it worked!