LinkBack URL
About LinkBacks
I am attempting to sum column C cells if column B cells fall between specific time of day.
My formula =SUMIFS(C1:C12753,B1:B12753,">=10:00:00 AM",B1:B12753,"<=11:00:00 AM") regardless how I change the times returns 0.Book1.xlsx
I am attempting to sum column C cells if column B cells fall between specific time of day.
My formula =SUMIFS(C1:C12753,B1:B12753,">=10:00:00 AM",B1:B12753,"<=11:00:00 AM") regardless how I change the times returns 0.Book1.xlsx
Col_B contains DATES and time.
Try something like this:
Is that something you can work with?
Please Login or Register to view this content.
The issue is that column B has a date attached to it also even though it is formatted not to show it. You need to strip that date off first. Try SUMPRODUCT
=SUMPRODUCT($C$1:$C$12753, --(MOD($B$1:$B$12753,1)>="10:00 AM"+0), --(MOD($B$1:$B$12753,1)<="11:00 AM"+0))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That appears to be working...Thank You!Originally Posted by Ron Coderre
Col_B contains DATES and time.
Try something like this:
Is that something you can work with?Please Login or Register to view this content.
We stumbled on a math error in Excel that happens occaissionally, probably due to the limitations of binary math.
In your list, two entries have a time of 10 AM (B3151 and B4356) and one entry has a time of 11 AM (B7993)...BUT they are not included in the total!
If you apply the MOD function to those 10 AM items, it returns: 0.416666666664241
Yet, the decimal value for 10 AM is: 0.416666666666667...so, no match
Consequently, the total is off by 64 (2 + 26 + 36)
It should be: 88,206
This formula returns the correct value:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks