Summing a column based on date (1 Viewer)

Scatman

Registered User.
Local time
Today, 18:41
Joined
Feb 11, 2000
Messages
27
I have a date column and a cost column and I would like to get a monthly total of cost.

Here is an example of how my worksheet is set up:

Our_Cost Completion_Date
$3000 7/14/2004
$0 9/27/2005
$1288 10/25/2004

I would like to use a formula to give me the total cost of a single month. I tried a SumProduct formula but it gave me the #Value!

Any Ideas as to how this can be done. I know this has to be simple, I'm just stumped.


Thanks
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:41
Joined
Aug 11, 2003
Messages
11,696
1) create a cell (column) with your month in it
2) Use sumif, using your value + the new column.

Good luck.
 

Rickster57

Registered User.
Local time
Today, 11:41
Joined
Nov 7, 2005
Messages
431
Sum the cost

I assume you have SEVERAL rows of data and a column for cost and a column with the date. Sort your data by the date, insert a row after the end of each month and sum the column containing the cost for each month. It will group everything by month and have a clean look. Hope this helps.

Rickster57
 

Scatman

Registered User.
Local time
Today, 18:41
Joined
Feb 11, 2000
Messages
27
I think this will only work for a specific month, but it will not give me totals by month and year i.e. September of 2004. I tried using the Date function as the criteria in the sumif but it will only allow for a single day not an entire month. any ideas how to get it to grab the month AND year I'm looking for?

Thanks for the help thus far...I think its getting me closer
 

Scatman

Registered User.
Local time
Today, 18:41
Joined
Feb 11, 2000
Messages
27
Rickster57 said:
I assume you have SEVERAL rows of data and a column for cost and a column with the date. Sort your data by the date, insert a row after the end of each month and sum the column containing the cost for each month. It will group everything by month and have a clean look. Hope this helps.

Rickster57

This might work, however I have a few rows that do not have completed dates and when I enter them, won't I have to re-sort my data each time I input a new completed date?

I really don't want to do that if it can be avoided. Thanks for the help.
 

shades

Registered User.
Local time
Today, 13:41
Joined
Mar 25, 2002
Messages
516
Two ways to approach this.

1. Using SUMIF:

Add a column (say C) to give the month, and use this formula starting at C2:

=MONTH(B2)

Copy down.

Then use Reference cell as D2: D13 (to put the month number)

In E2 put this formula (and copy down to E13)

=SUMIF($C$2:$C$100,D2,$A$2:$A$100)

2. Use SUMPRODUCT:

=SUMPRODUCT(($B$2:$B$100>=First Day of Month)*($B$2:$B$100<=Last Day of Month),$A$2:$A$100)

Note that you have two conditions for the Date.

=SUMRPDOCT(($B$1:$B$100>=1)
________
Arizona Dispensary
 
Last edited:

shades

Registered User.
Local time
Today, 13:41
Joined
Mar 25, 2002
Messages
516
Just a note: I was in a hurry with my above post.

Just to clarify: Please note that "First Day of the Month" and "Last Day of the Month" would actually have to be Date references or cell references with dates in them.
________
F40
 
Last edited:

Scatman

Registered User.
Local time
Today, 18:41
Joined
Feb 11, 2000
Messages
27
Shades,
Your formula is EXACTLY what I was using yesterday and it would not work. Today it does. I knew that the formula should work because I am using it in other places on my worksheet and it works perfectly.

Nothing more frustrating that knowing you are doing the right thing and not getting the answer you should be.

Anyway, it's working now and thanks to all for your help.
 

shades

Registered User.
Local time
Today, 13:41
Joined
Mar 25, 2002
Messages
516
Scatman said:
Shades,

Nothing more frustrating that knowing you are doing the right thing and not getting the answer you should be.

Been there more often than I care to admit. ;)

Anyway, it's working now and thanks to all for your help.

That's the best news!:)
________
Honda Cmx250C
 
Last edited:

Users who are viewing this thread

Top Bottom