# Check if date is between 2 dates

I see a solution for this question using visual basic, however I am looking to use it in an Excel function. In the example attached I want to sum a range of values if the date is between two other dates.

I know I can sum if the date is greater or less than a specified date:

=SUMIF(\$A2:\$A4,"<39814",C\$2:C\$4) where the criteria is the Excel date as a serial number (in this case, before 1/1/09).

How can I do this if I want to sumif the date is between two dates? I tried this:

=SUMIF(\$A2:\$A4,AND("<398149",">=39448"),B\$2:B\$4) where the criteria is supposed to be any date in 2008.

I get a result of 0.

Also, I want to reference cells containing the date, not the Excel date serial value. See the attached example.

Sumif-example.xls
Asked:
###### Who is Participating?

Commented:
...obviously you wouldn't normally have the criteria dates as part of the criteria range, that was just by way of example....criteria dates, A4 and A5 should ideally be in different cells. Note that for a whole year only you could use YEAR function like this:

=SUMPRODUCT((YEAR(A3:A6)=2008)+0,B3:B6)

regards, barry
0

Commented:
You can use SUMPRODUCT like this

=SUMPRODUCT((A3:A6>=A4)*(A3:A6<=A5),B3:B6)

regards, barry
0

Commented:
or from excel version 2003 you could also use

=SUMIFS(B2:B6;A2:A6;"<40179";A2:A6;">39813")

to get the total for 2009
0

Data ManagerCommented:
Or if you really like sumif

=SUMIF(A2:A6,"<="&\$A\$5,B2:B6)-SUMIF(A2:A6,"<"&\$A\$4,B2:B6)
0

Commented:
Plug this formula into your cell
=SUM(IF(AND(A2>=A15,A2<A16),B2,0),IF(AND(A3>=A15,A3<A16),B3,0),IF(AND(A4>=A15,A4<A16),B4,0),IF(AND(A5>=A15,A5<A16),B5,0),IF(AND(A6>=A15,A6<A16),B6,0))

The long-winded way.
0

Author Commented:
Exactly what I was looking for thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.