Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating revenue based on accounting months | Excel Discussion (Misc queries) | |||
Calculating Month To Date Revenue | Excel Worksheet Functions | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Calculating an Activities During Periods Of Natural Months and/or Dates | Excel Worksheet Functions |