Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
I have a spreadsheet that contains two columns, one is the date (m/yyyy
format) and the other contains cost associated with the date. I would like to have a running total by year of the associated cost entered for that date. So when I enter in a new date/cost, (ie: 4/2008, cost=$1200) my 'total' cell for 2008 would then add this new cost in. I know there is a way to do this, just am stumped as to how. I'm thinking I would use the 'sumif' function? If anyone can tell me what my formula would be, I would appreciate the help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
One way:
=SUMPRODUCT(--(YEAR(A1:A100)=2008),B1:B100) -- Biff Microsoft Excel MVP "Amber" wrote in message ... I have a spreadsheet that contains two columns, one is the date (m/yyyy format) and the other contains cost associated with the date. I would like to have a running total by year of the associated cost entered for that date. So when I enter in a new date/cost, (ie: 4/2008, cost=$1200) my 'total' cell for 2008 would then add this new cost in. I know there is a way to do this, just am stumped as to how. I'm thinking I would use the 'sumif' function? If anyone can tell me what my formula would be, I would appreciate the help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf Function
Formatting of an Excel date means nothing. An Excel date is a number. You
might want to read some documentation of Excel before using Excel. You are confusing formatting with content. If I format my birthdate as dd/mm/yy or yy/dd/mm, the date remains the same. You can format 1.33 as 1.3300000 or $0001.33 yet it is the same 1.33. Formatting is for human consumption. Perhaps the Excel Bible series by John Walkenbach would be of assistance to you. If you have entered your dates in Excel date format, then =SUMPRODUCT(--(YEAR(A1:A50)=2008),B1:B50) if your dates are in column A and your costs are in column B. Good luck. Tyro "Amber" wrote in message ... I have a spreadsheet that contains two columns, one is the date (m/yyyy format) and the other contains cost associated with the date. I would like to have a running total by year of the associated cost entered for that date. So when I enter in a new date/cost, (ie: 4/2008, cost=$1200) my 'total' cell for 2008 would then add this new cost in. I know there is a way to do this, just am stumped as to how. I'm thinking I would use the 'sumif' function? If anyone can tell me what my formula would be, I would appreciate the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Can SUMIF function include AND function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |