how to add figures from multiple worksheets in excel using ranges

[gary2511]

Hi
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<A2),sheet1!H4:sheet1!H500)

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<A2),sheet1:sheet3!H4:sheet1:sheet3!H500)

and also thrown in some extra brackets to be safe

=SUMPRODUCT(--((sheet1:sheet3!F4):(sheet1:sheet3!F500>=A1)),--((sheet1:sheet3!F4):(sheet1:sheet3!F500)<A2),(sheet1:sheet3!H4):(sheet1:sheet3!H500))

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

any ideas would be appreciated

Thanx in advance
Gary

[stanleydgromjr]

gary2511,

Welcome to the Excel Forum.


How about:

=SUMPRODUCT(--(Sheet1!F4:F500>=A1),--(Sheet1!F4:F500<A2),Sheet1!H4:H500)+SUMPRODUCT(--(Sheet2!F4:F500>=A1),--(Sheet2!F4:F500<A2),Sheet2!H4:H500)+SUMPRODUCT(--(Sheet3!F4:F500>=A1),--(Sheet3!F4:F500<A2),Sheet3!H4:H500)+SUMPRODUCT(--(Sheet4!F4:F500>=A1),--(Sheet4!F4:F500<A2),Sheet4!H4:H500)


Or, see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[gary2511]

gary2511,

Welcome to the Excel Forum.


How about:

=SUMPRODUCT(--(Sheet1!F4:F500>=A1),--(Sheet1!F4:F500<A2),Sheet1!H4:H500)+SUMPRODUCT(--(Sheet2!F4:F500>=A1),--(Sheet2!F4:F500<A2),Sheet2!H4:H500)+SUMPRODUCT(--(Sheet3!F4:F500>=A1),--(Sheet3!F4:F500<A2),Sheet3!H4:H500)+SUMPRODUCT(--(Sheet4!F4:F500>=A1),--(Sheet4!F4:F500<A2),Sheet4!H4:H500)


Or, see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hi
Thanks for the answer and I have already thought of that one but there are actually 40 sheets in the spreadsheet and I just simplified it for posting.

There will be new sheets inserted in the range at a later date so I need the formula to be dynamic.

I have attached a sheet with a simple example of what I am trying to achieve

Gary

[gary2511]

I have included a new example with my non working formulas on

[gary2511]

oops wrong one doh!

here is the example with my attempts in

[stanleydgromjr]

gary2511,


Detach/open workbook GetAllTotals - all ws into summary w SUMPRODUCT - gary2511 - EF837977 - SDG15.xlsm and run the GetAllTotals macro.


You should be able to add/delete worksheets, except the summary worksheet, and the grand total of alll the worksheets (except summary) will go into cell H3 in the summary worksheet.


You may have to edit the Sumproduct formula in the macro, the ranges of cells to work with.