sum of values that fall in a month

[klc]

I want to add the values of column "B" when the corresponding cell in column "A" date falls in "April

A
2 Apr 04 $10
6 Apr 04 $50
3 May 04 $20

Thanks in advance

 

[Ron Rosenfeld]

I want to add the values of column "B" when the corresponding cell in column "A" date falls in "April"

A B
2 Apr 04 $100
6 Apr 04 $500
3 May 04 $200

Thanks in advance

Something like:

=SUMIF(A1:A100,">="&DATE(2004,4,1),B1:B100) -
SUMIF(A1:A100,">"&DATE(2004,4,30),B1:B100)


--ron

 

[DDM]

KLC, here's another way: =SUMPRODUCT((MONTH(A1:A3)=4)*(B1:B3))
--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I want to add the values of column "B" when the corresponding cell in

column "A" date falls in "April"

 

[klc]

thanks that worked... do you know of anyway to have that work over multiple sheets in a workbook???

 

[Ron Rosenfeld]

thanks that worked... do you know of anyway to have that work over multiple sheets in a workbook???

Not easily.

SUMIF is not a function that works with 3D references. And it would depend on
precisely how your worksheets are set up.

If dates are always in the same place on each sheet, you could somehow derive
that information from one worksheet, and then use SUM with the 3D reference.

If the date locations vary on each sheet, it would probably be simplest to use
a VBA routine.

Another option would be to have a summary area on a single worksheet, and then
use SUMIF in that area.


--ron