SOLVED

XLOOKUP + SUM

%3CLINGO-SUB%20id%3D%22lingo-sub-1538543%22%20slang%3D%22en-US%22%3EXLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538543%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20good%20people%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20an%20excel%20sheet%20and%20trying%20to%20make%20an%20XLOOKUP%20formula%20which%20will%20add%20the%20values%20for%20a%20particular%20email%20address%20(Test%20sheet%20attached)%20-%20I%20have%20tried%20to%20incorporate%20SUM%20with%20XLOOKUP%20formula%20but%20it%20is%20not%20calculating%20the%20different%20values%20but%20only%20giving%20the%20first%20one%20it%20finds.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESearched%20online%20however%20I%20couldn't%20find%20a%20proper%20solution%20for%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EGerald%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1538543%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538600%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727906%22%20target%3D%22_blank%22%3E%40geraldvassallo%3C%2FA%3E%26nbsp%3B%20Use%20SUMIF%20instead.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538718%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494140%22%20target%3D%22_blank%22%3E%40wsantos%3C%2FA%3E%26nbsp%3BThank%20you%20for%20that%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20It%20seems%20to%20be%20working%20better%20now%2C%20however%2C%20when%20using%20it%20with%20the%20main%20sheet%20I'm%20using%2C%20the%20calculations%20are%20somewhat%20wrong%20and%20not%20showing%20the%20true%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20assistance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538782%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494140%22%20target%3D%22_blank%22%3E%40wsantos%3C%2FA%3E%26nbsp%3Bto%20give%20you%20a%20further%20idea%2C%20in%20this%20sheet%2C%20%3CA%20href%3D%22mailto%3Auser3%40gmail.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Euser3%40gmail.com%3C%2FA%3E%26nbsp%3Bshould%20have%20a%20value%20of%202%2C%20however%2C%20the%20formula%20is%20coming%20up%20with%20a%20value%20of%200.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539037%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727906%22%20target%3D%22_blank%22%3E%40geraldvassallo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(Hours!%24C%3A%24C%2CHours!%24B%3A%24B%2C%24A2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539062%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20this!!%20Very%20helpful!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539147%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20%2B%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727906%22%20target%3D%22_blank%22%3E%40geraldvassallo%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello good people,

 

I am working on an excel sheet and trying to make an XLOOKUP formula which will add the values for a particular email address (Test sheet attached) - I have tried to incorporate SUM with XLOOKUP formula but it is not calculating the different values but only giving the first one it finds. 

 

Searched online however I couldn't find a proper solution for this. 

 

Would appreciate any help. 

 

Thanks,

Gerald

6 Replies
Highlighted

@geraldvassallo  Use SUMIF instead.

Highlighted

@wsantos Thank you for that It seems to be working better now, however, when using it with the main sheet I'm using, the calculations are somewhat wrong and not showing the true value. 

 

Thank you for your assistance. 

Highlighted

@wsantos to give you a further idea, in this sheet, user3@gmail.com should have a value of 2, however, the formula is coming up with a value of 0. 

 

 

Highlighted
Best Response confirmed by geraldvassallo (Occasional Contributor)
Solution

@geraldvassallo 

That is

=SUMIFS(Hours!$C:$C,Hours!$B:$B,$A2)
Highlighted

@Sergei Baklan Thank you for this!! Very helpful! 

Highlighted

@geraldvassallo , you are welcome