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

%3CLINGO-SUB%20id%3D%22lingo-sub-1598675%22%20slang%3D%22en-US%22%3Ecalculating%20different%20values%20in%20excel%20like%20present%2C%20present%20%2B%201%20and%20displaying%20their%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598675%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20this%20is%20Achin%20Kansal%20from%20india%26nbsp%3B%3C%2FP%3E%3CP%3EI%20run%20a%20hotel%20and%20have%20made%20a%20sheet%20in%20excel%20for%20attendance%20of%20my%20staff%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20applied%20basic%20countif%20formula%20to%20count%20total%20present%20days%20but%20now%20I%20want%3C%2FP%3E%3CP%3E%22%20if%20any%20employee%20does%20double%20shift%22%20then%20how%20will%20we%20calculate%20that%20extra%20attendance%20and%20thus%20recalculating%20his%20salary%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20My%20attached%20sheet%20%22%20August%202020%20attendance.xlsx%20please%20check%20column%20B36%20and%20double%20click%20it%2C%20so%20u%20can%20see%20the%20formula%20of%20Count%20if%20in%20this%20cell.%20Now%20what%20I%20want%20is%20if%20in%20any%20employee%20i%20type%20P%2B1%20which%20indicates%20that%20this%20employee%20has%20done%20overtime%20on%20this%20particular%20date%2C%20then%20this%20column%20value%20should%20be%20added%20to%20Column%20B36%20.%20For%20ex.%20right%20now%20the%20total%20displayed%20in%20column%20B36%20is%2017%20so%20if%20I%20type%20P%2B1%20in%20suppose%20column%20B20%20then%20the%20total%20in%20column%20B36%20should%20change%20from%2017%20to%2018%20.%20I%20hope%20I%20am%20clear.%20Open%20for%20expert%20advice%20on%20this%20problem.%3C%2FP%3E%3CP%3EMany%20Thanks%3C%2FP%3E%3CP%3EEmail%20%3A-%20achinkansal%40gmail.com%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1598675%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599988%22%20slang%3D%22en-US%22%3ERe%3A%20calculating%20different%20values%20in%20excel%20like%20present%2C%20present%20%2B%201%20and%20displaying%20their%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764435%22%20target%3D%22_blank%22%3E%40achinkansal2580%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(B3%3AB33%2C%22P*%22)%2BCOUNTIF(B3%3AB33%2C%22*%2B1%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20%22P%2B1%22%20is%20the%20only%20variant%20for%20overtime%2C%20otherwise%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(B3%3AB33%2C%22P*%22)%2BSUMPRODUCT(1*IF(LEFT(B3%3AB30%2C2)%3D%22P%2B%22%2CRIGHT(B3%3AB30)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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