Calculating revenue per month by aggregating dates

C

Commutervet

I have column headings for DATE, $RATE,UNITS PER WK,NUMBER OF WEEKS, TOTAL.

At the bottom I have cells for JAN, FEB, MAR etc.
I want the month cells to automatically aggregate the total $ for that
calendar month by looking for the dates in the DATE column and adding the
totals from the TOTAL column. There may be multiple January dates in the date
column so there will be multiple rows with January relevant totals.

I need a formula to place in each MONTH cell that will generate the info I
need.
Help???
 
B

bpeltzer

I'd calculate each month using two SUMIF functions, one to add up everything
on or after the beginning of the month and another to subtract from that
everything on or after the beginning the the next month. For January:
=sumif(a:a,">="&date(2006,1,1),e:e)-sumif(a:a,">="&date(2006,2,1),e:e)
Only the month numbers would change for subsequent months (til the last,
when the final date becomes date(2007,1,1).
 
C

Commutervet

I may be doing something wrong. When I entered your parameters it is
returning a date rather than the sum of the last column.
 
C

Commutervet

My bad...the cell was formatted for dates rather than currency. When I fixed
the cell formatting the correct answer appeared. Thanks!
 
C

Commutervet

The formula is returning a date rather than the sum of the last column. Am I
doing something wrong?
 
C

Commutervet

Column entries are
1/2/2006,$35,12,2,$840
1/16/2006,$35,12,1,$105
2/1/2006,$35,4,1,$140
3/16/2006,$35,6,1,$210

Jan cell result: $210
Looks like it is picking up the last item in the last column only.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top