Calculating revenue per month by aggregating dates
 

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???
--
Kent In Houston

Calculating revenue per month by aggregating dates
 

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).

"Commutervet" wrote:

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???
--
Kent In Houston

Calculating revenue per month by aggregating dates
 

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


"bpeltzer" wrote:

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).

"Commutervet" wrote:

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???
--
Kent In Houston

Calculating revenue per month by aggregating dates
 

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


"bpeltzer" wrote:

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).

"Commutervet" wrote:

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???
--
Kent In Houston

Calculating revenue per month by aggregating dates
 

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


"bpeltzer" wrote:

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).

"Commutervet" wrote:

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???
--
Kent In Houston

Calculating revenue per month by aggregating dates
 

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.
--
Kent In Houston


"bpeltzer" wrote:

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).

"Commutervet" wrote:

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???
--
Kent In Houston