how to add figures from multiple worksheets in excel using ranges

0
votes

I have multiple worksheets all using the same template I have a summary page and have a formula that works for an individual sheet

=SUMPRODUCT(--(sheet1!F4:sheet1!F500>=A1),--(sheet1!F4:sheet1!F500

the formula checks the relevant ranges on sheet 1 and returns the values in the boxes in the date range specified in A1 and A2

The problem is that I want to do this for multiple sheets and use a 3D reference.

I have tried

=SUMPRODUCT(--(sheet1:sheet3!F4:sheet1:sheet3!F500>=A1),--(sheet1:sheet3!F4:sheet1:sheet3!F500

and also thrown in some extra brackets to be safe

=SUMPRODUCT(--((sheet1:sheet3!F4):(sheet1:sheet3!F500>=A1)),--((sheet1:sheet3!F4):(sheet1:sheet3!F500)

neither of these work and I am getting the #NAME? error

any ideas would be appreciated

Thanx in advance
Gary

1
excelexcel-formula

1 Answers

0
votes

Your formulas look like they are truncated, are you just trying to count dates between A1 and A2 inclusive? You can't use 3D references in SUMPRODUCT. In Excel 2007 you could use this formula

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&G1:G3&"'!F4:F500"),">="&A1,INDIRECT("'"&G1:G3&"'!F4:F500"),"<="&A2))

where you have all your sheet names listed in G1:G3