# Sumif with Date Criteria not working

#### ChrisCione

##### Board Regular
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

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.

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

They both need to be the same

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.

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!

Replies
4
Views
3K
Replies
20
Views
930
Replies
7
Views
346
Replies
2
Views
504
Replies
2
Views
213

1,207,255
Messages
6,077,307
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back