| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumifs with data and dates - I can not figure out the dates
I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 $326.00 Inventory 1/15/2010 $250.00 Utilities 1/15/2010 $98.00 Rent 1/29/2010 $1,000.00 Rent 1/30/2010 $536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials...f-xaml-an.aspx |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumifs with data and dates - I can not figure out the dates
One way...
=SUMIF(B1:B10,"="&DATE(2010,1,1),C1:C10)-SUMIF(B1:B10,""&DATE(2010,12,31),C1:C10) Better to use cells to hold the date boundaries: E1 = 1/1/2010 F1 = 12/31/2010 =SUMIF(B1:B10,"="&E1,C1:C10)-SUMIF(B1:B10,""&F1,C1:C10) Another way... =SUMPRODUCT(--(YEAR(B1:B10)=2010),C1:C10) -- Biff Microsoft Excel MVP <gary davis wrote in message ... I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 $326.00 Inventory 1/15/2010 $250.00 Utilities 1/15/2010 $98.00 Rent 1/29/2010 $1,000.00 Rent 1/30/2010 $536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials...f-xaml-an.aspx |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumifs with data and dates - I can not figure out the dates
You can create a pivot table and simply only turn on that year.
Advanced filtering will work too. I think that you can also use grouping, but I do not know what version of excel you are working with. Pivot table is the easiest. On Mon, 18 Jan 2010 17:53:46 -0800, gary davis wrote: I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 $326.00 Inventory 1/15/2010 $250.00 Utilities 1/15/2010 $98.00 Rent 1/29/2010 $1,000.00 Rent 1/30/2010 $536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials...f-xaml-an.aspx |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumifs with data and dates - I can not figure out the dates
Assuming you are using xl-2007
=SUMIFS(C:C,B:B,"="&DATE(2010,1,1),B:B,"<="&DATE( 2010,12,31)) "gary davis" wrote: I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 $326.00 Inventory 1/15/2010 $250.00 Utilities 1/15/2010 $98.00 Rent 1/29/2010 $1,000.00 Rent 1/30/2010 $536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials...f-xaml-an.aspx . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIFS formula using dates and text as criteria | Excel Discussion (Misc queries) | |||
| sumifs between 2 specific dates entered. | Excel Worksheet Functions | |||
| SUMIFS and dates | Excel Worksheet Functions | |||
| sumifs, sumif with dates | Excel Worksheet Functions | |||
| SUMIFS with dates | Excel Worksheet Functions | |||
Linear Mode
