Formula Help: Monthly Statistics

[Mike Copeland]

I have several spreadsheets with information similar to this:
Date Cost Odometer
01/08/2011 43.00 97,950
01/19/2011 51.00 98,256
01/28/2011 46.80 98,527
02/10/2011 51.00 98,810
02/24/2011 55.30 99,115
03/04/2011 56.50 99,416
03/16/2011 58.50 99,703
03/30/2011 61.75 99,997
04/13/2011 57.00 100,267
04/24/2011 61.20 100,560
05/04/2011 66.00 100,856
05/17/2011 63.50 101,136
05/25/2011 58.75 101,428

I can produce yearly totals (by establishing a line with some
computations on a range), but I'd like to compute monthly totals based
on the Date column. I looked at some Excel FAQs without finding (what
must be) a common formula for such a thing. I don't know how to
parse/evaluate the "month" part of an Excel date variable. Please
advise. TIA

 

[Ron Rosenfeld]

I have several spreadsheets with information similar to this:
Date Cost Odometer
01/08/2011 43.00 97,950
01/19/2011 51.00 98,256
01/28/2011 46.80 98,527
02/10/2011 51.00 98,810
02/24/2011 55.30 99,115
03/04/2011 56.50 99,416
03/16/2011 58.50 99,703
03/30/2011 61.75 99,997
04/13/2011 57.00 100,267
04/24/2011 61.20 100,560
05/04/2011 66.00 100,856
05/17/2011 63.50 101,136
05/25/2011 58.75 101,428

I can produce yearly totals (by establishing a line with some
computations on a range), but I'd like to compute monthly totals based
on the Date column. I looked at some Excel FAQs without finding (what
must be) a common formula for such a thing. I don't know how to
parse/evaluate the "month" part of an Excel date variable. Please
advise. TIA

Perhaps a Pivot Table?

Insert/Pivot Table

Drag Date to Rows area, Cost and Odometer to Values area.

Cost Field Setting should be SUM
Odometer Field Setting should be MAX

Right click on a Date; select Group / Months

Format to taste. Here's an example of the output given your input.



Date Cost Odometer
Jan $140.80 98,527
Feb $106.30 99,115
Mar $176.75 99,997
Apr $118.20 100,560
May $188.25 101,428
Grand Total $730.30 101,428

 

[Peter T]

Another way

In a column, write the last dates of the month. Tip write the 1st's of the
month, then =A1-1 where A1 is the first of those dates, then paste special
values to where you want the last dates of the months.

With the last dates of months starting in E3 down, and your data in A2:C14
with headers in row1

F3: =SUMPRODUCT(($A$2:$A$14<=E3)*($A$2:$A$14>E2)*$B$2:$B$14)

Note the relative/absolute addressing though you may find it easier to use
named ranges, or 2007/2010 Table addressing

One more thing, E2 should be empty or zero or say 31 Dec 2010

Peter Thornton

 

[Mike Copeland]

Another way
In a column, write the last dates of the month. Tip write the 1st's of the
month, then =A1-1 where A1 is the first of those dates, then paste special
values to where you want the last dates of the months.

With the last dates of months starting in E3 down, and your data in A2:C14
with headers in row1

F3: =SUMPRODUCT(($A$2:$A$14<=E3)*($A$2:$A$14>E2)*$B$2:$B$14)

Note the relative/absolute addressing though you may find it easier to use
named ranges, or 2007/2010 Table addressing

One more thing, E2 should be empty or zero or say 31 Dec 2010

This works nicely. Thanks!