• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Question about SUMIF with Date Comparison as criteria

Pete Wright

Member
Hi Everybody!

I have a sheet with my working hours and I want to sum up one day's working time

The table is very simple:
A = Date
B = Start Time
C = End Time
D = Hours
E = Sum of Working Hours per Day


The Hours are calculated as follows:
=IF(OR(ISBLANK(B3),ISBLANK(C3)),"",IF(C3<B3,24+24*(C3-B3),24*(C3-B3)))
For row nr 3 => 24 * (C3-B3)

To keep things simple there are only 4 possible entries for the decimals:
X.00 X.25 X.50 X.75

But there can be more than just one row for a specific Date.
Example:

____A________D______E_
07/06/2018___3:00
07/06/2018___2:30___5,5
07/07/2018___6:00___6,0
07/08/2018___5:45___5,75
07/09/2018___4:00
07/09/2018___1:00___5,00

The problem is column E. I have no idea how to achieve this.
I once had a worksheet with things figured out, but I lost it on one of my many hard drives...

As far as I remember, I used the SUMIF function.
So I tried this:
=SUMIF($A$1:$A$6,"A1<>A2",$D$1:$D$6)
what I think it does:
compare if two consecutive cells have different dates and if they do, sum up all above
what Excel does:
Go to hell, I ain't sum up nothing here :-(

Does anybody know how to get the job done?

Besides:
It should only contain formulas available in Excel for Android, since I type the working hours on my mobile and print them out at the end of a month at home.


Many thanks in advance
 
Try this in E2
=SUMPRODUCT((A2<>A3)*($A$2:A2=A2)*($D$2:D2))

Thank you very much!

It does exactly what I need it to!
SUMPRODUCT seems to be a very powerful function since I saw it so often before.

I added some conditional formatting to the rest of the cells to get a nice "design".

Nevertheless, is there a way to achieve the same result with SUMIF?
 
Pete

Unfortunately, Sum/Countif/s don't allow calculations on the Range within the function

You can get around that by adding a helper column which would tag the end of each block of data
 
Back
Top