sumproduct and dates

P

Peter

How do you include dates ranges in a sumproduct formula and what's the
format. eg: Column A has many dates in the format 08/11/04. I want to sum the
values in column B where the dates in A are in the range 01/01/04 to 31/12/04.
Many thanks
Peter
London, UK
 
F

Frank Kabel

Hi
=SUMPRODUCT(--(A1:A100>=DATE(2004,1,1)),--(A1:A100<=DATE(2004,12,31)),B1:B100)

or
=SUMPRODUCT(--(YEAR(A1:A100)=2004),B1:B100)

or
=SUMIF(A1:A100,">=" & DATE(2004,1,1),B1:B100)-SUMIF(A1:100,">" &
DATE(204,12,31),B1:B100)
 
D

Don Guillett

I usually try to put the date for selection in a cell and use the cell
reference in the formula.
 
Top