# SUMIF Based on Dates

#### DJ'sGiGi

##### Board Regular
I need help with a SUMIF based on dates formula. I've compared my formula to another one the forum where the user was attempting to do exactly the same thing I'm trying to do. I have a column with dates and one with % of the day worked. I need to sum the time for cells with the same date to make sure the person has not worked more than 1.0 day on any given date. Columns A-C of the data and the formula I'm using look like this:

<PRE>
9/16/2014 OFF 0.00 "=SUMIF(\$A\$6:\$A\$18,A6,\$B\$6:\$B\$18)"
9/17/2014 0.50 0.50
9/17/2014 0.75 0.75
9/18/2014 0.25 0.25
9/18/2014 0.00
0.00
0.00

</PRE>

As you can see, Excel is not totaling correctly because I should have 1.25 for 9/14/2014.

Your help and suggestions are appreciated.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Your formula Works perfect, it gives 1.25 for date 09/17/14

Geez Louis!! It's totally not working for me. The results in the post are the one's I'm getting. Did you lay your data out exactly like in my post?

Yes, exactly like your's. Look for formats in column "B"

my formula on "C7":
Code:
``=SUMIF(\$A\$6:\$A\$18,A7,\$B\$6:\$B\$18)``
gives 1.25

Yes, exactly like your's. Look for formats in column "B"

my formula on "C7":
Code:
``=SUMIF(\$A\$6:\$A\$18,A7,\$B\$6:\$B\$18)``
gives 1.25

Thanks Sahak. I appreciate you verifying that my formula is correct.

I don't know what the deal is. I opened a new workbook and retyped the data and formula and it worked fine. For some reason it just won't work in my existing workbook. May just have to start from scratch.

Good.

Make sure the dates in your first workbook are actual dates and not formatted as text.

After spending about 2 weeks trying to figure out the problem with the post below, I've discovered that sumif is not working because the dates are a formula-based calculation. If I type over the formula and input values. the formula works fine. Is there any reason why Excel won't recognize the cells with the formula?

I need help with a SUMIF based on dates formula. I've compared my formula to another one the forum where the user was attempting to do exactly the same thing I'm trying to do. I have a column with dates and one with % of the day worked. I need to sum the time for cells with the same date to make sure the person has not worked more than 1.0 day on any given date. Columns A-C of the data and the formula I'm using look like this:

<PRE>
9/16/2014 OFF 0.00 "=SUMIF(\$A\$6:\$A\$18,A6,\$B\$6:\$B\$18)"
9/17/2014 0.50 0.50
9/17/2014 0.75 0.75
9/18/2014 0.25 0.25
9/18/2014 0.00
0.00
0.00

</PRE>

As you can see, Excel is not totaling correctly because I should have 1.25 for 9/14/2014.

Your help and suggestions are appreciated.

Replies
3
Views
220
Replies
1
Views
141
Replies
28
Views
539
Replies
9
Views
277
Replies
3
Views
134

1,221,308
Messages
6,159,161
Members
451,543
Latest member
cesymcox

### 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