How to Total the number of hours-worked on each date?

[Mark53]

Hello, people,

I studied the SUBTOTAL thing, but... maybe that's not what should be used here?

In the attached sample file, I want the Total of hours worked on each day, in column P.
That is,... when column B changes, put a total of the column H hours in column P.

In the file, columns O and P and Q were manually created.
Column O shows the number of time-spans (rows) for any certain date.
Column P is what I want to automatically calculate...
it shows the total number of hours worked on that date.
Column Q shows the overtime $amount that I should get (at the $16.5/hour rate,
with time-&-a-half for hours 8.01 thru 10, and double-time above 10 hours.)

HOW to automatically calculate column P?

Thanks, in advance.

Mark53

[humdingaling]

combination of countif/sumif should do the trick

in O5

=IF(COUNTIF($B$5:B5,B5)=0,"",IF(COUNTIF($B$5:B5,B5)=COUNTIF(B:B,B5),COUNTIF($B$5:B5,B5),""))

in p5

=IF(O5="","",SUMIFS(H:H,B:B,B5))

in column Q
not quite sure the logic is but the formula in there doesnt seem to work on certain lines

[Mark53]

Brilliant.
Even after studying that COUNTIF thing for a while, I can barely understand how it works, but it seems to.
And the SUMIFS thing is almost as confusing. Ah, well...
Thanks very much, Humdingaling. Merry Christmas.
Mark53

[humdingaling]

=IF(COUNTIF($B$5:B5,B5)=0,"",IF(COUNTIF($B$5:B5,B5)=COUNTIF(B:B,B5),COUNTIF($B$5:B5,B5),""))

the first part of the formula it just to keep it tidy

IF(COUNTIF($B$5:B5,B5)=0,""

basically for rows with no dates

so the next bit

IF(COUNTIF($B$5:B5,B5)=COUNTIF(B:B,B5),COUNTIF($B$5:B5,B5),""))

so what i am doing here is comparing the number of dates on the list

COUNTIF(B:B,B5)

vs
number of dates up to the current row

COUNTIF($B$5:B5,B5)

$B$5:B5
changes to
$B$5:B6 in row 6
$B$5:B7 in row 7
etc
so as it goes along the range it looks at increases accordingly

as such counts of date up to the current row

so the if formula is check ...if the amount of dates in the whole list is the same current row
COUNTIF($B$5:B5,B5)=COUNTIF(B:B,B5)

if you understand and prefer countif syntax
then you can substitute it with sumif instead (in your case)
https://www.techonthenet.com/excel/formulas/sumif.php
basically the syntax is the same as countif
SUMIF( range, criteria, [sum_range] )


sumifs i just used because i prefer using sumifs instead of sumif
https://www.techonthenet.com/excel/formulas/sumifs.php
SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
whereby the sum range goes first BUT you can add more criteria should you require it

sumif is limited to 1 criteria

[humdingaling]

Merry xmas to you too
thanks for the rep