Forum Discussion
Solved
How to Sum the result of Matched return as SUMPRODUCT Didn't work.
how can i get the 121 in this example? SUMPRODUCT didn't work.
Yes, without double dash it will even more shorter
=SUMPRODUCT((A2:A8=D14)*ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)
When one more for the collection
=SUMPRODUCT((A2:A8=D14)*NOT(ISNA(MATCH(B1:J1,A15:C15,0)))*B2:J8)
- Or more simplified
=SUMPRODUCT((A2:A8=D14)*--ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)- Thank you so much everyone. I am so amazed with number of way this problem could be solved.
I am kind of perplexed :) I am not sure which formula should i use between these three.
Can you recommend which one should I use?
This =SUM(INDEX($B$2:$J$8,MATCH($D$14,$A$2:$A$8,0),N(CHOOSE(1,MATCH($A$15:$C$15,$B$1:$J$1,0)))))
Or this =SUMPRODUCT((A2:A8=D14)*NOT(ISNA(MATCH(B1:J1,A15:C15,0)))*B2:J8)
Or this =SUMPRODUCT((A2:A8=D14)*--ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)Jamil, thank you.
Flora, any one which you understand better (even if it is more complex). Don't forget about the maintenance, most probably sooner or later you'd like to modify it or apply somewhere else. More familiar you with formula more time you save in such case. All the rest is for the training.
Yes, without double dash it will even more shorter
=SUMPRODUCT((A2:A8=D14)*ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)
