SUNDAY CALCULATION

%3CLINGO-SUB%20id%3D%22lingo-sub-2252077%22%20slang%3D%22en-US%22%3ESUNDAY%20CALCULATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252077%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20sir%3C%2FP%3E%3CP%3Ewhen%20we%20were%20preparing%20the%20salary%20sheet%20every%20time%20we%20have%20to%20replace%20cell%20reference%20work.%20So%20we%20want%20to%20know%20how%20to%20do%20a%20shortcut%20way%20to%20add%20all%20Sunday%20in%20monthly%20sheet.%26nbsp%3B%20%26nbsp%3Bif%20you%20have%20any%20formula%20regarding%20the%20auto-identify%20the%20%22S%22%20and%20go%20down%20the%20same%20cell%20where%20is%20representing%20the%20employee%20duty%20like%20(absent%20or%20Present%20)%2C%20and%20calculate%20the%20Sunday%20or%20Holidays.%20We%20are%20using%20the%200%20is%20present%20%26amp%3B%20%22A%22%20is%20absent.%26nbsp%3B%3C%2FP%3E%3CP%3E%7B0%3D1%2C%20A%3D0%7D%3C%2FP%3E%3CP%3Efile%20is%20attached%20for%20your%20reference.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2252077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2252118%22%20slang%3D%22en-US%22%3ERe%3A%20SUNDAY%20CALCULATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252118%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1016120%22%20target%3D%22_blank%22%3E%40Ashutosh9600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Sunday%20use%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(%24B%242%3A%24AF%242%2C%22S%22%2CB3%3AAF3%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20for%20Holiday%20use%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(%24B%242%3A%24AF%242%2C%22H%22%2CB3%3AAF3%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EFile%20is%20also%20attached%20for%20your%20reference.%20Please%20let%20me%20know%20if%20it%20works%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2252491%22%20slang%3D%22en-US%22%3ERe%3A%20SUNDAY%20CALCULATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1016120%22%20target%3D%22_blank%22%3E%40Ashutosh9600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWhat%20Mr.%20tauqeeracma%20sent%20you%20should%20be%20enough%20for%20you%20to%20plan.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENonetheless%2C%20with%20everyone's%20permission%2C%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20am%20sending%20you%20an%20old%20file%20that%20I%20made%20a%20long%20time%20ago%20as%20a%20vacation%20planner%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Ein%20addition%20to%20Mr.%20tauqeeracma%20suggestion%2C%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Emaybe%20that%20will%20give%20you%20additional%20ideas.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ESome%20things%20in%20the%20file%20are%20in%20German%2C%20it%20would%20have%20to%20be%20changed%20a%20little%20to%20meet%20your%20needs%20and%20translate%20%3A).%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThank%20you%20for%20your%20patience%20and%20understanding%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2252906%22%20slang%3D%22en-US%22%3ERe%3A%20SUNDAY%20CALCULATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252906%22%20slang%3D%22en-US%22%3EThanks%20Mr.%20Tauqeer%3CBR%20%2F%3EI've%20use%20this%20formula%20so%20many%20times%20different%20equations%20but%20don't%20think%20to%20apply%20this%20formula%20in%20salary%20sheet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2252907%22%20slang%3D%22en-US%22%3ERe%3A%20SUNDAY%20CALCULATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252907%22%20slang%3D%22en-US%22%3EYes%20that's%20enough.%3CBR%20%2F%3Ethankyou%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear sir

when we were preparing the salary sheet every time we have to replace cell reference work. So we want to know how to do a shortcut way to add all Sunday in monthly sheet.   if you have any formula regarding the auto-identify the "S" and go down the same cell where is representing the employee duty like (absent or Present ), and calculate the Sunday or Holidays. We are using the 0 is present & "A" is absent. 

{0=1, A=0}

file is attached for your reference. 

4 Replies

Hi @Ashutosh9600 

 

For Sunday use 

=COUNTIFS($B$2:$AF$2,"S",B3:AF3,0)

 

and for Holiday use 

=COUNTIFS($B$2:$AF$2,"H",B3:AF3,0)

File is also attached for your reference. Please let me know if it works as desired.

 

Thanks

Tauqeer

@Ashutosh9600 

What Mr. tauqeeracma sent you should be enough for you to plan.

Nonetheless, with everyone's permission,

I am sending you an old file that I made a long time ago as a vacation planner

in addition to Mr. tauqeeracma suggestion,

maybe that will give you additional ideas.

 

Some things in the file are in German, it would have to be changed a little to meet your needs and translate :).

 

Thank you for your patience and understanding

 

Nikolino

Thanks Mr. Tauqeer
I've use this formula so many times different equations but don't think to apply this formula in salary sheet.
Yes that's enough.
thankyou