Aug 19 2020 05:54 AM - edited Aug 19 2020 05:59 AM
Hi this is Achin Kansal from india
I run a hotel and have made a sheet in excel for attendance of my staff,
I have applied basic countif formula to count total present days but now I want
" if any employee does double shift" then how will we calculate that extra attendance and thus recalculating his salary
In My attached sheet " August 2020 attendance.xlsx please check column B36 and double click it, so u can see the formula of Count if in this cell. Now what I want is if in any employee i type P+1 which indicates that this employee has done overtime on this particular date, then this column value should be added to Column B36 . For ex. right now the total displayed in column B36 is 17 so if I type P+1 in suppose column B20 then the total in column B36 should change from 17 to 18 . I hope I am clear. Open for expert advice on this problem.
Many Thanks
Email :- achinkansal@gmail.com
Aug 19 2020 02:52 PM
That could be
=COUNTIF(B3:B33,"P*")+COUNTIF(B3:B33,"*+1")
assuming "P+1" is the only variant for overtime, otherwise like
=COUNTIF(B3:B33,"P*")+SUMPRODUCT(1*IF(LEFT(B3:B30,2)="P+",RIGHT(B3:B30),0))