SUMIF between dates
 

I have a worksheet with a column of dates (A) and a column of values (B).

How can I SUMIF between a date range.

Eg Sumif ( A 1/1/04 < 30/06/04, B)

In other words what is the total of values between this date range?

Bruce

Sumproduct would work;

=SUMPRODUCT((A1:A6DATEVALUE("1/1/2004"))*(A1:A6<DATEVALUE("4/1/2004")),B1:B
6)

You could also use one sumif counting "greater than" first date and then
subtract a sumif counting "greater than" second date if you want to, e.g.

=SUMIF(A1:A6,""&DATEVALUE("1/1/2004"),B1:B6)-SUMIF(A1:A6,""&DATEVALUE("4/1
5/2004"),B1:B6)



"Bruce" wrote in message
...
I have a worksheet with a column of dates (A) and a column of values (B).

How can I SUMIF between a date range.

Eg Sumif ( A 1/1/04 < 30/06/04, B)

In other words what is the total of values between this date range?

Bruce

Hi Dave
better to use DATE or a date format such as YYYY-MM-DD as other wise
your formula will not work for all country settings

--
Regards
Frank Kabel
Frankfurt, Germany


Dave R. wrote:
Sumproduct would work;


=SUMPRODUCT((A1:A6DATEVALUE("1/1/2004"))*(A1:A6<DATEVALUE("4/1/2004"))
,B1:B
6)

You could also use one sumif counting "greater than" first date and
then subtract a sumif counting "greater than" second date if you want
to, e.g.


=SUMIF(A1:A6,""&DATEVALUE("1/1/2004"),B1:B6)-SUMIF(A1:A6,""&DATEVALUE
("4/1
5/2004"),B1:B6)



"Bruce" wrote in message
...
I have a worksheet with a column of dates (A) and a column of values
(B).

How can I SUMIF between a date range.

Eg Sumif ( A 1/1/04 < 30/06/04, B)

In other words what is the total of values between this date range?

Bruce

Better off with SumIf...

E2: 1/1/04
F2: 30/06/04

=SUMIF(A:A,"="&E2,B:B)-SUMIF(A:A,""&F2,B:B)


Bruce Wrote:
I have a worksheet with a column of dates (A) and a column of values
(B).

How can I SUMIF between a date range.

Eg Sumif ( A 1/1/04 < 30/06/04, B)

In other words what is the total of values between this date range?

Bruce


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320327