Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2061039%22%20slang%3D%22en-US%22%3EVlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061039%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spread%20sheet%20where%20I%20register%20hours%20worked%20at%20certain%20jobs%20daily%20and%20each%20spreadsheet%20covers%20a%20weekly%20basis.%20The%20issue%20is%20a%20want%20to%20get%20daily%20totals%20and%20then%20weekly%20totals.%20If%20I%20use%20the%20filter%20function%20it%20removes%20cells%20that%20contain%20time%20from%20another%20person%20but%20on%20another%20day%20of%20the%20week.%20The%20issue%20im%20having%20with%20Vlookup%20is%20that%20about%2020%20clients%20make%20up%2090%25%20of%20the%20work%20so%20when%20I%20search%20for%20company%20like%20apple%20there%20will%20be%2015%20results%20on%20a%20single%20day.%20Is%20there%20a%20way%20to%20get%20a%20sum%20for%20all%20the%20vlookup%20values%20or%20another%20way%20of%20solving%20my%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2061039%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061098%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061098%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F929124%22%20target%3D%22_blank%22%3E%40Jeff_Kimrey%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Without%20knowing%20the%20specifics%2C%20in%20general%2C%20you%20should%20consider%20using%20SUMIFS%20instead%20of%20VLOOKUP.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20further%20assistance%2C%20in%20general%2C%20when%20you%20have%20a%20question%20about%20function%20usage%2C%20you%20should%20provide%20a%20detailed%20example%20that%20shows%20representative%20data%2C%20any%20formulas%20that%20you%20use%2C%20and%20expected%20results%2C%20which%20you%20might%20need%20to%20calculate%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20best%20way%20to%20do%20that%20in%20this%20forum%20is%20to%20attach%20an%20example%20Excel%20file%20(not%20an%20image)%2C%20which%20is%20redacted.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2079422%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3BI%20attached%20an%20example%20spread%20sheet%20what%20I%20am%20trying%20to%20end%20up%20with%20is%20weekly%20totals%20of%20hours%20worked%20for%20specific%20clients.%20Like%20how%20many%20hours%20worked%20for%20apple%20this%20week.%20I%20know%20it%20is%20possible%20with%20filter%20and%20sub%20total%20but%20I%20can%20only%20get%20daily%20totals%20and%20then%20I%20would%20have%20to%20repeat%20this%20serval%20times%20with%20different%20clients%20from%20my%20understanding.%26nbsp%3B%20My%20preferred%20outcome%20if%20possible%20is%20just%20have%20a%20list%20at%20the%20bottom%20that%20has%20weekly%20hours%20worked%20for%20each%20company.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a spread sheet where I register hours worked at certain jobs daily and each spreadsheet covers a weekly basis. The issue is a want to get daily totals and then weekly totals. If I use the filter function it removes cells that contain time from another person but on another day of the week. The issue im having with Vlookup is that about 20 clients make up 90% of the work so when I search for company like apple there will be 15 results on a single day. Is there a way to get a sum for all the vlookup values or another way of solving my issue.

3 Replies

@Jeff_Kimrey   Without knowing the specifics, in general, you should consider using SUMIFS instead of VLOOKUP.

 

If you want further assistance, in general, when you have a question about function usage, you should provide a detailed example that shows representative data, any formulas that you use, and expected results, which you might need to calculate manually, if you can.

 

The best way to do that in this forum is to attach an example Excel file (not an image), which is redacted.

@Joe User I attached an example spread sheet what I am trying to end up with is weekly totals of hours worked for specific clients. Like how many hours worked for apple this week. I know it is possible with filter and sub total but I can only get daily totals and then I would have to repeat this serval times with different clients from my understanding.  My preferred outcome if possible is just have a list at the bottom that has weekly hours worked for each company. 

@Jeff_Kimrey   IMHO, it would be better to have separate columns labeled date (A), employee (B), hours (C), client (D).  Then you could use a formula of the form:

 

=SUMIFS(C2:C200, D2:D200, "apple")

 

For more general summary tables, see F1:M7 and F9:L17 in Sheet4 in the attached file.

 

In fact, I suspect you could use pivot tables to construct such summaries.  I am not a PT person.  So I cannot help you with that.  And beware:   there are downsides to using PT; for example, I believe they do not auto-calculate(!).  For that reason, I do not like PTs.

 

-----

 

With your design in Sheet1, you could use a formula of the form:

 

=SUMPRODUCT(B2:N100,--(C2:O100="apple"))