Sumif ?

[C]

Sample Problem:

Date Cost
9/30/2003 $100
9/21/2003 $150
5/30/2003 $ 75
4/27/2003 $145
1/15/2003 $120
9/28/2002 $ 95
7/11/2002 $250

Other than doing a pivot, what's the easiest way of
getting the sum of the Cost for only September 2003?

Thank you!

 

[Peo Sjoblom]

One way

=SUMPRODUCT((YEAR(A2:A20)=2003)*(MONTH(A2:A20)=9),B2:B20)

 

[C]

Peo,

Thanks for this! However, what if the list is constantly
being added on. Other than editing my formula each time
something new is added, I'm planning to just say "A:A"
instead of "A2:A20". But, when I do that it gives me an
error message "#Num!". Any workaround?

Thanks!
C

 

[Peo Sjoblom]

You can either use a dynamic range and name it, assuming that you have dates
in A2 :A

do insert>name>define and put a name in the name box and use a formula like
this

=OFFSET($A$2,,,COUNT($A:$A),)

create another one called MyRange2, repeat what you did with the first but
use this formula


=OFFSET(MyRange,,1)

then you can do


=SUMPRODUCT((YEAR(MyRange)=2003)*(MONTH(MyRange)=9),MyRange2)

or use

A2:A65536 and B2:B65536

 

[Guest]

thanks!

-----Original Message-----
You can either use a dynamic range and name it, assuming that you have dates
in A2 :A

do insert>name>define and put a name in the name box and use a formula like
this

=OFFSET($A$2,,,COUNT($A:$A),)

create another one called MyRange2, repeat what you did with the first but
use this formula


=OFFSET(MyRange,,1)

then you can do


=SUMPRODUCT((YEAR(MyRange)=2003)*(MONTH(MyRange) =9),MyRange2)

or use

A2:A65536 and B2:B65536



--

Regards,

Peo Sjoblom





.