Formula for totals between certain dates?

[harriesh]

I'm sure this is a simple one for you guys but I've been trying for the last hour or so. What I have is a column A with dates and a column B with values. I need a formula that will give me the total value of only those that are within certain dates. e.g. 01/01/2010 & 28/02/2010. Many thanks.

 

[Mike Blackman]

Hi and welcome to the board,

Something like this maybe?

=SUMPRODUCT(--(A1:A26>=DATE(2010,1,5)),--(A1:A26<=DATE(2010,1,10)),B1:B26)

 

[stefankemp]

Try "=SUMPRODUCT((A:A<=DATE(2010,2,28))*(A:A>=DATE(2010,1,1))*B:B)

 

[harriesh]

Thanks for the VERY quick response. Worked a treat.

 

[Mike Blackman]

Harriesh,

Please note that with Stefan's formula whole columns can only be used within SUMPRODUCT in Excel 2007 onwards. Also its quite inneficient to have the formula calculating for every row when you probably only need a few rows within the formula.

 

[harriesh]

Using the following formula, if I replace the date within the bracktets to a cell instead, it doesn't like it. How can I insert a cell instead of the actual date?

=SUMPRODUCT(--(A1:A26>=DATE(2010,1,5)),--(A1:A26<=DATE(2010,1,10)),B1:B26)

 

[Mike Blackman]

Hi,

Like this;

=SUMPRODUCT(--(A1:A26>=C1),--(A1:A26<=C2),B1:B26)

The DATE function has these arguments DATE(Year,Month,Day)