calculating different values in excel like present, present + 1 and displaying their sum

Copper Contributor

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

 

1 Reply

@achinkansal2580 

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))