# Summing by Matching Parts of a Value to a Matrix

1. ## Summing by Matching Parts of a Value to a Matrix

Dear Friends,
I create summary data and the issue is they used different format.

Regards,
Wie

2. ## Re: Summing by Matching Parts of a Value to a Matrix

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

3. ## SUM or SUMIF formula needed

Dear Friends,
I create summary data and the issue is they used different format and type

Regards,
Wie

4. ## Re: Summing by Matching Parts of a Value to a Matrix

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)

6. ## Re: Summing by Matching Parts of a Value to a Matrix

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

7. ## Re: Summing by Matching Parts of a Value to a Matrix

Dear All,
Thanks for your advise but I forgot to put PO NO. I need total quantity model with same PO NO.
Here I attached new file. Thank you in advance for your help.

Regards,
Wie

8. ## Re: Summing by Matching Parts of a Value to a Matrix

=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

9. ## Re: Summing by Matching Parts of a Value to a Matrix

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)

10. ## Re: Summing by Matching Parts of a Value to a Matrix

Hi Ali,
Thank to for Ravi.

Regards,
Wie

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1