Home

Excel: How to return the total number of values in a table column based on date

%3CLINGO-SUB%20id%3D%22lingo-sub-658881%22%20slang%3D%22en-US%22%3EExcel%3A%20How%20to%20return%20the%20total%20number%20of%20values%20in%20a%20table%20column%20based%20on%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658881%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20is%20meant%20to%20track%20employee%20vacation%20days.%26nbsp%3B%20The%20leftmost%20Column%20contains%20employee%20names%2C%20and%20the%20Column%20Headers%20contains%20dates%20for%20the%20current%20scheduling%20period.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20easily%20total%20the%20number%20of%20employees%20who%20are%20on%20vacation%20on%20a%20given%20date.%26nbsp%3B%20This%20might%20include%20regular%20vacation%2C%20stat%2C%20etc.%26nbsp%3B%20I%20need%20to%20be%20able%20to%20choose%20a%20date%2C%20and%20have%20excel%20return%20the%20number%20of%20employees%20who%20have%20vacation%20on%20that%20date.%26nbsp%3B%20I%20do%20not%20need%20to%20be%20able%20to%20distinguish%20between%20the%20number%20of%20employees%20on%20stat%20v.%20vacation%2C%20etc.%2C%20I%20just%20need%20the%20total%20number%20of%20employees%20who%20will%20be%20off%20that%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20up%20a%20much%20smaller%20example%20of%20what%20I'm%20talking%20about%20and%20attached%20a%20photo%20so%20you%20understand%20what%20I%20am%20looking%20for.%26nbsp%3B%20Photo%201%20shows%20the%20spreadsheet%20itself%2C%20and%20Photo%202%20shows%20the%20calculator.%26nbsp%3B%20B1%20is%20a%20currently%20a%20dropdown%20list%20of%20dates.%26nbsp%3B%20The%20dropdown%20list%20comes%20from%20the%20range%20of%20dates%20on%20the%20sheet%20in%20Photo%201.%26nbsp%3B%20B2%20is%20where%20I%20want%20to%20show%20the%20total%20%23%20of%20employees%20who%20have%20STAT%2C%20VAC%2C%20or%20VAC%2FSTAT.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116606iCB46DC1732072F5C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Photo%201.jpg%22%20title%3D%22Photo%201.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116607i85DDC43912C17027%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Photo%202.jpg%22%20title%3D%22Photo%202.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-658881%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659205%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20How%20to%20return%20the%20total%20number%20of%20values%20in%20a%20table%20column%20based%20on%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659205%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F352103%22%20target%3D%22_blank%22%3E%40jmcniven860%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%20data%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20642px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116643iEFBE63D6A2A9FA90%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUM(COUNTIF(INDEX(%24C%3A%24H%2C0%2CMATCH(%24K2%2C%24C%241%3A%24H%241%2C0))%2C%7B%22VAC%22%2C%22STAT%22%2C%22VAC%2FSTAT%22%7D))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
jmcniven860
Occasional Visitor

I have a spreadsheet is meant to track employee vacation days.  The leftmost Column contains employee names, and the Column Headers contains dates for the current scheduling period.

 

I need to be able to easily total the number of employees who are on vacation on a given date.  This might include regular vacation, stat, etc.  I need to be able to choose a date, and have excel return the number of employees who have vacation on that date.  I do not need to be able to distinguish between the number of employees on stat v. vacation, etc., I just need the total number of employees who will be off that day.

 

I made up a much smaller example of what I'm talking about and attached a photo so you understand what I am looking for.  Photo 1 shows the spreadsheet itself, and Photo 2 shows the calculator.  B1 is a currently a dropdown list of dates.  The dropdown list comes from the range of dates on the sheet in Photo 1.  B2 is where I want to show the total # of employees who have STAT, VAC, or VAC/STAT.

 

Thanks in advance for any help!

Photo 1.jpg

 

Photo 2.jpg

 

1 Reply
Highlighted

@jmcniven860 

For such sample data

image.png

formula could be

=SUM(COUNTIF(INDEX($C:$H,0,MATCH($K2,$C$1:$H$1,0)),{"VAC","STAT","VAC/STAT"}))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies