SOLVED

summing of vlookup values

%3CLINGO-SUB%20id%3D%22lingo-sub-3129459%22%20slang%3D%22en-US%22%3Esumming%20of%20vlookup%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ask%20microsoft.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345875i25B9D8AFD70E9FD3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ask%20microsoft.jpg%22%20alt%3D%22ask%20microsoft.jpg%22%20%2F%3E%3C%2FSPAN%3ECan%20I%20have%20a%20better%20option%20for%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(I3%3D%22%22%2C0%2CVLOOKUP(I3%2CSheet1!%24C%3A%24D%2CCOLUMNS(Sheet1!%24C%3A%24D)%2CFALSE))%2BIF(J3%3D%22%22%2C0%2CVLOOKUP(J3%2CSheet1!%24C%3A%24D%2CCOLUMNS(Sheet1!%24C%3A%24D)%2CFALSE))%2BIF(K3%3D%22%22%2C0%2CVLOOKUP(K3%2CSheet1!%24C%3A%24D%2CCOLUMNS(Sheet1!%24C%3A%24D)%2CFALSE))%26nbsp%3B%26nbsp%3B%20and%20so%20on%20repeating%20for%20n%20times.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3129459%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3129545%22%20slang%3D%22en-US%22%3ERe%3A%20summing%20of%20vlookup%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1299569%22%20target%3D%22_blank%22%3E%40eliaskjohn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((I3%3AL3%3DSheet1!C%3AC)*Sheet1!D%3AD)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20this%20formula%20as%20shown%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Can I have a better option for

 

ask microsoft NEW.jpg

8 Replies

@eliaskjohn 

=SUMPRODUCT((I3:L3=Sheet1!C:C)*Sheet1!D:D)

 

Maybe with this formula as shown in the attached file.

Thank you. But it doesn't seems to be what my requirement is. I have just updated my query. can you please check it again?
best response confirmed by eliaskjohn (Occasional Contributor)
Solution

@eliaskjohn 

=SUMPRODUCT((D2:G2=$A$2:$A$7)*$B$2:$B$7)

 

Maybe with this formula.

@Quadruple_Pawn Thank you so much.
one more query. while copying the formula to another cells, following error is displayed. we can't even save the file without deleting the copied formula.ask microsoft 02.jpg

@eliaskjohn 

That's difficult to say without seeing the file or the formula in column H. Can you communicate the formula you entered in cell H8? Probably too many calculations are required to return the result of the formula. 

Thank you dear. @Quadruple_Pawn. I have sorted it. That error was because the formula was referring to the entire column. I have one more doubt. In the same way can we count the values instead of summing.ASK count.jpg Please see the attached image.

@eliaskjohn 

=SUMPRODUCT(MMULT(($B$2:$B$13=G8)*($C$2:$E$13=$H$7),ROW($1:$3)^0))

You are welcome. Glad you sorted out the error. The above formula seems to count the values as intended.

@Quadruple_Pawn 

Thank you so much for your support. It works perfectly. Though I didn’t get the logic in the formula, it works perfectly for my requirement. Once again, my sincere thanks.