Summing by Matching Parts of a Value to a Matrix

Dear Friends,
I create summary data and the issue is they used different format.
Need advise SUM or SUMIF formula needed. Thank you in advance for your help

Regards,
Wie

Administrative Note:

The title you chose was causing a database error, so I have fixed it for you. However, it was a very poor title, so below is a reminder of what we expect.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional.)

This cannot be done with either SUM or SUMIF, which is why your original title was so poor. Don't assume you know what functions should be used: just explain what you are trying to achieve and then leave thee choice of function to those helping you.

=SUMPRODUCT((Plan!$B$2:$B$10=LEFT(A2,FIND("-",A2)-1))*(Plan!$C$1:$N$1=MID(A2,FIND("-",A2)+1,2)),Plan!$C$2:$N$10)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

or can try below formula
=SUMPRODUCT((Plan!$C$2:$N$10)*(Plan!$B$2:$B$10&"-"&Plan!$C$1:$N$1=$A2))

=SUMPRODUCT((Plan!$C$2:$N$10)*(Plan!$B$2:$B$10&"-"&Plan!$C$1:$N$1=$B2)*(Plan!$A$2:$A$10=A2))
try it

So you just need to add the extra criterion:

=SUMPRODUCT((Plan!$A$2:$A$10=A2)*(Plan!$B$2:$B$10=LEFT(B2,FIND("-",B2)-1))*(Plan!$C$1:$N$1=MID(B2,FIND("-",B2)+1,2)),Plan!$C$2:$N$10)