Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif formula based on dates
I have the following partial spreadsheet:
Date Total Hours Compensation 1/15/2008 11.00 $133.43 1/22/2008 12.15 $147.38 2/8/2008 12.00 $145.56 Is there an IF function that can be used to calculate the running total Compensation by month without using the subtotal function? I would like this total to automatically calculate when data is added to the spreadsheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif formula based on dates
=SUMIF(A:A,"=" & DATE(2008,1,1),C:C)-SUMIF(A:A,"=" & DATE(2008,2,1),C:C)
That adds the comp from all entries dated on or after 1/1/2008 and subtracts the total of all entries from 2/1/2008. What's left is the total from Jan '08. Note that the dates could instead be cell references with the 'from' and 'until' dates. "linnipen" wrote: I have the following partial spreadsheet: Date Total Hours Compensation 1/15/2008 11.00 $133.43 1/22/2008 12.15 $147.38 2/8/2008 12.00 $145.56 Is there an IF function that can be used to calculate the running total Compensation by month without using the subtotal function? I would like this total to automatically calculate when data is added to the spreadsheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif formula based on dates
One way:
To sum for January 2008: =SUMPRODUCT(--(MONTH(A1:A10)=1),--(YEAR(A1:A10)=2008),C1:C10) For other months just change =1 to whatever month number you're interested in. -- Biff Microsoft Excel MVP "linnipen" wrote in message ... I have the following partial spreadsheet: Date Total Hours Compensation 1/15/2008 11.00 $133.43 1/22/2008 12.15 $147.38 2/8/2008 12.00 $145.56 Is there an IF function that can be used to calculate the running total Compensation by month without using the subtotal function? I would like this total to automatically calculate when data is added to the spreadsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif Formula - Won't Recognize Dates | Excel Discussion (Misc queries) | |||
SumIf formula from one column based on criteria from two other col | Excel Discussion (Misc queries) | |||
Formula help - indirect & sumif & dates ..... | Excel Discussion (Misc queries) | |||
SUMIF & COUNTIF based on dates | Excel Worksheet Functions | |||
I want to use sumif to sum up a column based on a range of dates | Excel Worksheet Functions |