Sep 10 2021 08:04 AM
This might be complicated as I'm not extremely familiar with excel. I would like to create a formula that can find a name from the bottom table and depending on which section it is in (cleanup / setup etc.) I want it to be able to add 1 to the value assigned in the upper table. If this isn't possible, ill understand...
Sep 10 2021 08:37 AM
SolutionIn C25, confirmed with Ctrl+Shift+Enter to turn it into an array formula:
=SUMPRODUCT(($A$48:$A$61=C$24)*(IFERROR(FIND($B25,$B$48:$M$61),0)=1))
Fill to the right to F25, then fill down.
Sep 10 2021 08:54 AM
I forgot to mention: change D26 from 'Set up' to Setup, to be consistent with A52:A55.
Sep 10 2021 09:24 AM
Sep 10 2021 09:48 AM
It works for me - see the attached sample workbook (I didn't bother with the formatting)
Please note that Robbins occurs in the lower table but not in the upper one, so he is not counted.
Sep 10 2021 08:37 AM
SolutionIn C25, confirmed with Ctrl+Shift+Enter to turn it into an array formula:
=SUMPRODUCT(($A$48:$A$61=C$24)*(IFERROR(FIND($B25,$B$48:$M$61),0)=1))
Fill to the right to F25, then fill down.