Separated Totals Sheets

Question

I have an Excel 2007 workbook with its first sheet being my daily totals in this format:

Date   Subtotal   Tax   Total Sales

Now I'm working on Totals Sheets for Quarterly, Monthly, and Annually. I'd like to be able to simply use the same format for my other worksheets by putting the proper date criteria in the A column of the next sheet.

Example: Sheet 1 is Daily Totals. Columns are as above, A through D. On sheet 2 (Monthly Totals), I'd like the same format as above, and simply enter Aug 2011 in the Date field, then the total is automatic, based on the manually entered date.

I'm going cross-eyed playing with the SUMIF and DSUM functions. My biggest problem is that my shop is only open weekends, and thus far the only way I've been doing it is manually typing out each month's cell range. It's not a formula I can just copy down and be done with it.

Is there an effective way to automate this process please?

Answer 1

Same data as @Jack's left two columns plus same again with some upscaling, using PivotTable and Group:

Answer 2

Here's a solution for the month:

First create a column containing the first day of each month you want total sales for, in descending order. You can accomplish this with EOMONTH(TODAY(),-1)+1 to get the current month's first day. Then EDATE will give you previous months.

Then I just combined two SUMIF functions to get the sum of all sales between two dates:

=SUMIF(A:A,">"&G3,B:B) - SUMIF(A:A,">"&G2,B:B)

That formula goes in the H3 cell. The G3 cell holds the first day of the month I'm interested in, and the G2 cell holds the first of the next month.

date    total sales     month   subtotal
06/05/13    100     06/01/13    400
06/04/13    100     05/01/13    3100
06/03/13    100     04/01/13    3000
06/02/13    100     03/01/13    3100
06/01/13    100     02/01/13    2800
05/31/13    100     01/01/13    3100
05/30/13    100     12/01/12    3100
05/29/13    100     11/01/12    3000
05/28/13    100     10/01/12    3100
05/27/13    100     09/01/12    3000
05/26/13    100     08/01/12    3100
05/25/13    100     07/01/12    3100
05/24/13    100     06/01/12    3000
05/23/13    100     05/01/12    3100
05/22/13    100     04/01/12    3000
05/21/13    100     03/01/12    3100