Need help in finding the right formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3020030%22%20slang%3D%22en-US%22%3ENeed%20help%20in%20finding%20the%20right%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3020030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20working%20on%20shifting%20scheduling%20data.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20created%20a%20drop-down%20for%20the%20date%20and%20time.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ENow%2C%20what%20I%20need%20help%20with%20is%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Whenever%20I%20select%20the%20date%20and%20time(from%20the%20drop-down)%20the%20total%20number%20of%20employees%20available%20with%20their%20name%20shows%20up.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIs%20this%20possible%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20suggestions%20would%20be%20helpful.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%20in%20advance!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3020030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3024091%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20in%20finding%20the%20right%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3024091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1234048%22%20target%3D%22_blank%22%3E%40Zee996%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCHOOSE(WEEKDAY(%24N%245%2C16)%2CCOUNTIF(OFFSET(%24A%2410%3A%24A%2431%2C0%2CMATCH(%24N%244%2C%24B%248%3A%24AG%248%2C0))%2C%22Yes%22)%2CCOUNTIF(OFFSET(%24A%2436%3A%24A%2457%2C0%2CMATCH(%24N%244%2C%24B%2434%3A%24AG%2434%2C0))%2C%22Yes%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbove%20formula%20counts%20number%20of%20employees%20that%20are%20available%20either%20on%20saturday%20or%20sunday%20according%20to%20the%20date%20in%20cell%20N5.%20The%20formula%20can%20easily%20be%20adapted%20with%20further%20COUNTIF%20formulas%20to%20count%20available%20employees%20for%20monday%20to%20friday.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20entered%20conditional%20formatting%20for%20ranges%20A10%3AA31%20and%20A36%3AA57%20in%20order%20to%20highlight%20available%20employees%20according%20to%20the%20data%20and%20time%20selection%20by%20dropdown%20in%20N4%20and%20N5.%20So%20far%20i%20couldn't%20figure%20out%20if%20there%20is%20a%20way%20to%20have%20the%20names%20of%20available%20employees%20filtered.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,
I am working on shifting scheduling data.
I have created a drop-down for the date and time.

Now, what I need help with is
- Whenever I select the date and time(from the drop-down) the total number of employees available with their name shows up.
Is this possible?

Any suggestions would be helpful.

Thank you in advance!

1 Reply

@Zee996 

=CHOOSE(WEEKDAY($N$5,16),COUNTIF(OFFSET($A$10:$A$31,0,MATCH($N$4,$B$8:$AG$8,0)),"Yes"),COUNTIF(OFFSET($A$36:$A$57,0,MATCH($N$4,$B$34:$AG$34,0)),"Yes"))

 

Above formula counts number of employees that are available either on saturday or sunday according to the date in cell N5. The formula can easily be adapted with further COUNTIF formulas to count available employees for monday to friday.

 

I entered conditional formatting for ranges A10:A31 and A36:A57 in order to highlight available employees according to the data and time selection by dropdown in N4 and N5.