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

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

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

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
.