Sum multiple worksheets based on dates

August 25, 2009 at 16:43:57
Specs: Windows XP
I'm trying to add total charges from invoices calculated in several worksheets in Excel 2003.

What I've done thus far:

The following formula will look for the total sum in F32 in all worksheets between the "Start2009" and "End2009" worksheets and return the total value of F32 for all sheets.


Now I need to find total charges for a period of time. The following formula will look in column C cells to check the range of dates in the invoice on the same worksheet. If the requested date range is found (example: 01/01/09 to 03/31/09 a.k.a. 1st Quarter) the total sum in cell F32 from each invoice will be returned.


I can only do the above formula on the same worksheet in which the dates in C17:C31 and invoice total in F32 are entered. But I want to create a "Totals" worksheet at the end of the workbook that will give me updated quarterly totals pulling the data (based on dates query) from all worksheets, and as I create new worksheets for new billing invoices and insert between the Start2009 and End2009 worksheets the formula will updated the "Totals" worksheet. Is this clear? I basically need to combine the two formulae above with the ability to search multiple worksheets for dates in a range to return the value of F32.

I've tried SUMPRODUCT, but that didn't work. It's the date search that has me stymied.

Thanks for the help.

See More: Sum multiple worksheets based on dates

Report •

August 25, 2009 at 18:48:38
Are you sure your formula is correct?

As far as I can tell, one SUMIF looks for dates before or equal to 1/1/2009 and the other SUMIF looks for dates before 1/31/2009.

Doesn't that mean the answer will either be 0 or F32*(-1)?

I may also be misunderstanding your problem.

Why can't you use the formula in each sheet and then sum whatever it returns (in each sheet) in your totals sheet?

Report •

August 27, 2009 at 06:59:08
You're right, the formula should read: ">=" 01/01/09 and "<=" 01/31/09.

Your response made me realize that I hadn't thought of something else. I can see that simply referencing the TOTAL cell (F32) won't work for my problem.

The way the workbook is setup:

An invoice worksheet for each week (52 weeks/year), worksheet tab names are last day of the week;

Some weekly invoices could have the end/start of two months mid-week, ex. 09/30/09 (Wed) and 10/01/09 (Thur);

I want to be able to add and sum each invoice charge from the sub-totals in F17:F31 (AMOUNT column) based on dates in C17:C31 (Date column). This way, over the course of the year, rather than looking in each worksheet, I can go to the TOTALS worksheet to see the invoiced charges to date for each month, each quarter, entire year.

Worksheet (tab name: "100209")

ColC		ColF

09/28/2009	$450		
09/29/2009	$450
09/30/2009	$450
10/01/2009	$450
10/02/2009	$450

On "Totals" worksheet:

Month		Total	Quarter  Total      Year
January                   1        $0       $2250
February                  2        $1350
March                     3        $900
April                     4        $0
September	$1350
October		$900

Report •

August 27, 2009 at 11:43:36
The issue is that SUMIF does not work on 3D ranges like SUM does. You can either use SUMIF in each worksheet and then SUM those cells, or you can try something like the formula below.

It's not the final solution you need, but it shows how to make SUMIF work across multiple worksheets. You'll need to make it work for the date ranges you require.

As written, it will sum all cells in F17:F31 where C17:C31 is >=1/1/2009

= SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D52&"'!C17:C31"),">="&DATE(2009,1,1),INDIRECT("'"&D1:D52&"'!F17:F31")))

Where D1:D52 contain the sheet names that you are accessing.

Report •

Related Solutions

August 27, 2009 at 18:17:22
Thanks! I'll give it a go. I appreciate the help.

Report •

Ask Question