SOLVED

How to tally number of audits?

%3CLINGO-SUB%20id%3D%22lingo-sub-2000083%22%20slang%3D%22en-US%22%3EHow%20to%20tally%20number%20of%20audits%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000083%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20new%20to%20working%20with%20excel%20and%20I%20was%20given%20a%20task%20to%20create%20a%20new%20Daily%20Report%20for%20the%20facility%20that%20I%20work%20at.%20So%20far%20I%20have%20completed%2090%25%20of%20what%20I%20was%20asked%20to%20do%20but%20I%20cannot%20figure%20out%20how%20to%20tally%20the%20number%20of%20audits%2Freports%20each%20employee%20does%20in%20a%20daily%20basis.%20I%20have%20included%20a%20picture%20of%20a%20piece%20of%20the%20daily%20report%20that%20I%20have%20been%20working%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-17%20155952.png%22%20style%3D%22width%3A%20486px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241475i2F0AFE6DCA15047D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-17%20155952.png%22%20alt%3D%22Screenshot%202020-12-17%20155952.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20yellow%20boxes%20are%20drop%20down%20boxes%20that%20the%20supervisor%20has%20the%20option%20of%20typing%2Fclicking%20in%20the%20drop%20down%20list%20(combo%20box%20macro)%20to%20show%20who%20was%20at%20work%20for%20that%20day.%20As%20you%20can%20see%20you%20can%20have%20any%20order%20of%20employees%20in%20one%20day%20where%20Bill%20may%20be%20the%20first%20to%20clock%20in%20or%20maybe%20even%20last%20to%20clock%20in.%20I%20want%20to%20be%20able%20to%20tally%20however%20many%20times%20he%20completes%20an%20audit%20or%20report%20everyday%20into%20a%20conjunctive%20monthly%20tally%20chart%2C%20and%20yearly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20function%20that%20can%20accomplish%20this%20or%20perhaps%20a%20macro%3F%20Thank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2000083%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2000468%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20tally%20number%20of%20audits%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F905789%22%20target%3D%22_blank%22%3E%40CRick1997%3C%2FA%3E%26nbsp%3BNot%20convinced%20that%20the%20way%20you%20collect%20your%20data%20is%20the%20most%20effective%2C%20but%20from%20judging%20the%20screenshot%20you%20should%20be%20able%20to%20use%20SUMIF.%3C%2FP%3E%3CP%3EPersonally%2C%20I%20would%20collect%20all%20data%20in%20a%20single%20table%20(date%2C%20employee%2C%20%23audits%2C%23reports%2C%20etc.%20)%20and%20create%20summaries%20from%20that%20table.%20Per%20day%2C%20per%20employee%2C%20whatever%2C%20for%20example%2C%20by%20using%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20contains%20a%20mock-up%20of%20your%20schedule%20with%20a%20summary%20using%20SUMIF.%20From%20column%20L%20and%20onwards%2C%20I%20demonstrated%20the%20use%20of%20a%20single%20structured%20table%2C%20summarising%20it%20using%20a%20pivot%20table.%20You%20say%20you%20are%20new%20to%20Excel%2C%20so%20it%20may%20be%20a%20bit%20overwhelming%20to%20begin%20with.%20But%20I%20believe%20it's%20better%20to%20learn%20some%20of%20the%20basic%20features%20first%20and%20do%20thing%20right%20from%20the%20start.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2000940%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20tally%20number%20of%20audits%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2000940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you!%20This%20actually%20helped%20me%20a%20lot!%20I%20see%20why%20you%20said%20my%20chart%20wasn't%20the%20best%20for%20this%20task%2C%20so%20I%20changed%20it%20up%20a%20little%20bit%20so%20that%20I%20can%20just%20input%20the%20entire%20column%20into%20the%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-C%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am new to working with excel and I was given a task to create a new Daily Report for the facility that I work at. So far I have completed 90% of what I was asked to do but I cannot figure out how to tally the number of audits/reports each employee does in a daily basis. I have included a picture of a piece of the daily report that I have been working on. 

 

Screenshot 2020-12-17 155952.png

 

The yellow boxes are drop down boxes that the supervisor has the option of typing/clicking in the drop down list (combo box macro) to show who was at work for that day. As you can see you can have any order of employees in one day where Bill may be the first to clock in or maybe even last to clock in. I want to be able to tally however many times he completes an audit or report everyday into a conjunctive monthly tally chart, and yearly. 

 

Is there a function that can accomplish this or perhaps a macro? Thank you in advance!

 

-C

2 Replies
best response confirmed by CRick1997 (New Contributor)
Solution

@CRick1997 Not convinced that the way you collect your data is the most effective, but from judging the screenshot you should be able to use SUMIF.

Personally, I would collect all data in a single table (date, employee, #audits,#reports, etc. ) and create summaries from that table. Per day, per employee, whatever, for example, by using a pivot table.

 

The attached file contains a mock-up of your schedule with a summary using SUMIF. From column L and onwards, I demonstrated the use of a single structured table, summarising it using a pivot table. You say you are new to Excel, so it may be a bit overwhelming to begin with. But I believe it's better to learn some of the basic features first and do thing right from the start.

 

@Riny_van_Eekelen Thank you! This actually helped me a lot! I see why you said my chart wasn't the best for this task, so I changed it up a little bit so that I can just input the entire column into the function. 

 

-C