Sumif with Date Criteria not working

[ChrisCione]

I created a worksheet to track the vacation and sick time I use and earn at work.

Column B contains dates (dates I earned time off, essentially payday dates) and dates when I use the earned time.

Column F contains a numeric value representing the time earned; column H, the time used.

At the bottom I have current balance and a projected balance.

It looks something like this:

B	F	H
Date	Earned (in hours)	Used (in hours)
Jan 12, 2012	4
Jan 14, 2012		2
Jan 26, 2012	4
Feb 14, 2012	4
Feb 15, 2012		1
and so on........
Current Balance
Projected Balance

<tbody>
</tbody>

The formula for the projected balance is this: =SUM(F4:F53)-SUM(H4:H53). It adds all the anticipated hours I will earn for the year and then subtracts all the anticipated hours I will use, showing me a projected year-end balance. Works perfectly.

The formula for the current balance essentially does the same, EXCEPT that it is supposed to only run the calculations for any values corresponding to a date that is equal to or less than today's date (essentially, excluding any future time used and/or earned). It looks like this:

=SUM(SUMIF($B$5:$B$53,"<="&TODAY(),F4:F53))-SUM(SUMIF($B$5:$B$53,"<="&TODAY(),H4:H53))

It's not quite working. On my current sheet, I have a value entered for the date June 14th, which is less than today's date. But it won't include it in the calculation. if I put something in for June 12th, it will include that value.

I am stumped.

 

[T. Valko]

I created a worksheet to track the vacation and sick time I use and earn at work.

Column B contains dates (dates I earned time off, essentially payday dates) and dates when I use the earned time.

Column F contains a numeric value representing the time earned; column H, the time used.

At the bottom I have current balance and a projected balance.

It looks something like this:

B	F	H
Date	Earned (in hours)	Used (in hours)
Jan 12, 2012	4
Jan 14, 2012		2
Jan 26, 2012	4
Feb 14, 2012	4
Feb 15, 2012		1
and so on........
Current Balance
Projected Balance

<TBODY>
</TBODY>

The formula for the projected balance is this: =SUM(F4:F53)-SUM(H4:H53). It adds all the anticipated hours I will earn for the year and then subtracts all the anticipated hours I will use, showing me a projected year-end balance. Works perfectly.

The formula for the current balance essentially does the same, EXCEPT that it is supposed to only run the calculations for any values corresponding to a date that is equal to or less than today's date (essentially, excluding any future time used and/or earned). It looks like this:

=SUM(SUMIF($B$5:$B$53,"<="&TODAY(),F4:F53))-SUM(SUMIF($B$5:$B$53,"<="&TODAY(),H4:H53))

It's not quite working. On my current sheet, I have a value entered for the date June 14th, which is less than today's date. But it won't include it in the calculation. if I put something in for June 12th, it will include that value.

I am stumped.

You don't need the SUM functions but they won't effect the result in any way.

=SUMIF($B$5:$B$53,"<="&TODAY(),F4:F53)-SUMIF($B$5:$B$53,"<="&TODAY(),H4:H53)

That looks like what you need. If it's not returning the correct result then maybe check your data for true date values and true numeric values for the time entries.

 

[ChrisCione]

You don't need the SUM functions but they won't effect the result in any way.

=SUMIF($B$5:$B$53,"<="&TODAY(),F4:F53)-SUMIF($B$5:$B$53,"<="&TODAY(),H4:H53)

That looks like what you need. If it's not returning the correct result then maybe check your data for true date values and true numeric values for the time entries.

Thanks for replying. I removed the SUM function, no dice. I've already checked (but did it once again) that the dates are true date values and the same for the numbers. They are.

That's why I'm stumped. I cannot figure out why the formula won't work.

 

[Jonmo1]

Your ranges are not aligned
Your date range is beginning in row 5 while the sum ranges are beginning in row 4

They both need to be the same

 

[ChrisCione]

Your ranges are not aligned
Your date range is beginning in row 5 while the sum ranges are beginning in row 4

They both need to be the same

Eureka! Problem solved. I would have never figured it out.

Thanks so much; it was driving me absolutely insane.

 

[T. Valko]

Your ranges are not aligned
Your date range is beginning in row 5 while the sum ranges are beginning in row 4

They both need to be the same

Good catch!