SUMIF - dates, ignore times?

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,055
Office Version
  1. 365
Platform
  1. Windows
List of dates in a column, some have times, some do not. I think the ones that do not are due to the time being 00:00?

I want to use a SUMIF looking at that column, but looking only at the dates.

Is this possible? At the moment the user uses text to columns to split out the time into the next column, but I'd rather eliminate this step if possible.

Many thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Use SUMIFS and do something to the effect of

=SUMIFS([SumRang], [daterange], ">=" Day, [daterange], < Day + 1)

So sum everything after 00:00 on your day, but before the next day (so everything from that day)
 
Upvote 0
You can using SUMIFS. Greater than or equal to the date and less than the date +1.
 
Upvote 0
Maybe another option to use SUMPRODUCT:
Excel Workbook
ABCDE
1Date / TimeValue
25/6/2019100DateSum
35/6/2019 10:001015/8/2019103
45/6/2019 14:00102
55/8/2019103
66/12/2019104
76/12/2019105
86/12/2019 4:00106
96/17/2019107
106/17/2019 16:00108
Sheet
 
Upvote 0
Thanks all - ended up using sumproduct :)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top