+ Reply to Thread
Results 1 to 10 of 10

Summing by Matching Parts of a Value to a Matrix

  1. #1
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    163

    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
    Attached Files Attached Files
    Last edited by AliGW; 06-02-2020 at 11:41 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    Re: Summing by Matching Parts of a Value to a Matrix

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    163

    SUM or SUMIF formula needed

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

    Regards,
    Wie
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    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)

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    Re: Summing by Matching Parts of a Value to a Matrix

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

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,633

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

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    163

    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
    Attached Files Attached Files

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,633

    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. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    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. #10
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    163

    Re: Summing by Matching Parts of a Value to a Matrix

    Hi Ali,
    That is great. thanks for your help and advise.
    Thank to for Ravi.

    Regards,
    Wie

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  2. [SOLVED] Summing Tiles and Summing Tile Decors
    By makinmomb in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-09-2014, 12:15 AM
  3. [SOLVED] Help with Summing Duplicates, deleting rows after summing and filling col. for unsummed
    By solidrock1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 09:11 AM
  4. [SOLVED] Summing Query
    By CRAIGSUV in forum Excel General
    Replies: 7
    Last Post: 03-21-2014, 02:54 PM
  5. [SOLVED] Validation list summing query
    By alan peele in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2014, 08:56 PM
  6. Web Query - Change a word in query to form a new query
    By scottymelloty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 04:13 AM
  7. Summing Columns-revolves around summing particular items
    By savv32 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2005, 09:05 PM

Bookmarks

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