Forum Discussion

Flora Sim

Brass Contributor

Mar 13, 2018

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.

Book1.xlsx10 KB

Formulas & Functions

tips and tricks

SergeiBaklan
Mar 13, 2018
Yes, without double dash it will even more shorter

=SUMPRODUCT((A2:A8=D14)*ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)

Jamil

Bronze Contributor

Mar 13, 2018

Or more simplified

=SUMPRODUCT((A2:A8=D14)*--ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)

Flora Sim

Brass Contributor

Mar 13, 2018

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)

SergeiBaklan
Diamond Contributor
Mar 13, 2018
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.
- Flora Sim
  Brass Contributor
  Mar 13, 2018
  Many thanks Sergei!
  
  I was able to use that formula and it is short and simple. I learned a lot today.
  I am grateful to you people.
Jamil
Bronze Contributor
Mar 13, 2018
Sergei’s last formula is what you should use.
- Flora Sim
  Brass Contributor
  Mar 13, 2018
  Thanks very much to all of you.
  
  I ended up using this. =SUMPRODUCT((A2:A8=D14)*ISNUMBER(MATCH(B1:J1,A15:C15,0))*B2:J8)