You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Count hours next to a specific person ( word )

So, im totally clueless here, but im pretty sure that should be doable.


I need a formula that counts all the hours a worker works, and that the formula searches whole table each time something has been changed, if someone changes shifts etc, that’s just an example table in attachments but hopefully You’ll get the idea.


Let’s say i need to know how many hours have Hamza Kilic, so the formula should search the whole table for name Hamza Kilic and then check the box on the left of the name and count all of these hours together and put the number in O2, and just copy paste for each worker + when i update some of the hours / shifts, the whole formula updates automatically.


English ain’t my first language so sorry for all the mistakes :)

iPad Pro, iPadOS 16

Posted on Jun 11, 2023 5:52 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 11, 2023 9:05 AM

You can try something like this:




In Q2, copied into other columns as needed:


=SUMIF($D,Q$1,$C)+SUMIF($G,Q$1,$F)+SUMIF($J,Q$1,$I)


I first added new columns C, F, and I to hold the number of hours so that these can be added up. You can hide these columns later if you want, once you've got the entire table working.


In C2, copied down the column, and also copied to columns F and I:


=IFERROR(DUR2HOURS(TEXTAFTER(B2,"-")−TEXTBEFORE(B2,"-")),"")


In German this looks like this:



=SUMMEWENN($D,Q$1,$C)+SUMMEWENN($G,Q$1,$F)+SUMMEWENN($J,Q$1,$I)




=WENNFEHLER(DAUERINSTD(TEXTNACH(B2,"-")−TEXTVOR(B2,"-")),"")


SG

Similar questions

3 replies
Question marked as Top-ranking reply

Jun 11, 2023 9:05 AM in response to fotoramic

You can try something like this:




In Q2, copied into other columns as needed:


=SUMIF($D,Q$1,$C)+SUMIF($G,Q$1,$F)+SUMIF($J,Q$1,$I)


I first added new columns C, F, and I to hold the number of hours so that these can be added up. You can hide these columns later if you want, once you've got the entire table working.


In C2, copied down the column, and also copied to columns F and I:


=IFERROR(DUR2HOURS(TEXTAFTER(B2,"-")−TEXTBEFORE(B2,"-")),"")


In German this looks like this:



=SUMMEWENN($D,Q$1,$C)+SUMMEWENN($G,Q$1,$F)+SUMMEWENN($J,Q$1,$I)




=WENNFEHLER(DAUERINSTD(TEXTNACH(B2,"-")−TEXTVOR(B2,"-")),"")


SG

Count hours next to a specific person ( word )

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.