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)
12 Replies
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)
Try this: =SUMPRODUCT(($A$2:$A$8=$A$11)*($B$1:$D$1=$A$12)*($B$2:$D$8))+SUMPRODUCT(($A$2:$A$8=$A$11)*($B$1:$D$1=$A$13)*($B$2:$D$8))+SUMPRODUCT(($A$2:$A$8=$A$11)*($B$1:$D$1=$A$14)*($B$2:$D$8))
I've also attached an example file with the other formulas that were given as answers so you can further understand the differences and how they work.
- Thank you Matt. Appreciated!
Hi Flora,
you can simply get the result by using the formula below. with entering it with Control Shift Enter.
=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)))))Hi Flora
=SUMPRODUCT((A2:A8=D14)*((B1:J1=A15)+(B1:J1=B15)+(B1:J1=C15))*B2:J8)
returns 121
