summing of vlookup values

[eliaskjohn]

Can I have a better option for

[#2]

@eliaskjohn 

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

Maybe with this formula as shown in the attached file.

[#3]

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?

[#4 · Accepted solution]

@eliaskjohn 

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

Maybe with this formula.

[#5]

@OliverScheurich 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.

[#6]

@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. 

[#7]

Thank you dear. @OliverScheurich. 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. Please see the attached image.

[#8]

@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.

[#9]

@OliverScheurich 

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.