+ Reply to Thread
Results 1 to 14 of 14

Sumproduct with multiple criteria and partial text match help

  1. #1
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Sumproduct with multiple criteria and partial text match help

    I will preface this by saying that I am not the most educated when it comes to Excel and its formulae, so I will try my best to explain my predicament. I've taken a class where we learned basic excel formulae, but nothing as deep as a multiple criteria sumproduct function. What I need help on is this:

    I have multiple columns with values that I'm trying to sum if they match the criteria. One column contains a list of cities, another contains text variations of "public" and "private" (which is why I need a partial text match), and another that specifies whether it is connected to a network or not, and if so, it specifies which network (not important I just need to know if it is connected or not which is also why I'm looking for a partial text match), so I just need it to contain the word "network". On top of this, I have 3 columns that specify the number of different types of each product (call it Type1, Type2, and Type3). Based on hours of research, the formula I am currently attempting to tweak to fit my specific needs is this:

    =SUMPRODUCT((LEFT($L$2:$L$27,27)="Public ")*($V$2:$V$27="Non-Network")*($E$2:$E$27="City")*$S$2:$U$27)

    After Ford's Reply, I also attempted this SUMIFS formula:

    =SUMIFS(S2:U27,E2:E27,E3,L2:L27,"*Public*",V2:V27,"Non-Networked")

    Where column L contains public vs. private, column V contains types of network (or non-network), column E which contains the cities, and columns S:T which contain the number of each product. Ideally, I'm looking for a function where I can find the total number of Type1 products that are in city X, that are also private, and connected to a network.

    Can anybody think of something I can do here to make it work? There are so many entries that it will take days to do manually, so I'm looking for something to streamline the process. Any help or input is greatly appreciated.
    Attached Files Attached Files
    Last edited by Britt24; 07-11-2021 at 01:05 AM. Reason: Added sample workbook and clarification (hopefully)

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    It looks to me like you have edited the file and removed removed some columns - the description you give does not match the columns in te file?

    I have a feeling that you need to use SUMIFS (like it looks like you started to try - but you deleted some columns?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Re: Sumproduct with multiple criteria and partial text match help

    Thank you for the reply and the reccomendation for using a SUMIFS. I will edit my post to try to make it all make sense. This was my first one and got a message saying I needed to attatch a file so I made a dummy file real quick. I will re-make it to try to match my original description.

  4. #4
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Re: Sumproduct with multiple criteria and partial text match help

    If a moderator would not mind deleting this post so that I can re-make it, I would appreciate it. I don't want to overwhelm the forum with the same question, but I do not think this thread will get answered adequately due to my poor explanation.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    No need to start a new thread, we can keep going here

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    Maybe Im missing something, but it looks like you did not update your file - or description yet?

  7. #7
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Re: Sumproduct with multiple criteria and partial text match help

    Just updated, hopefully that makes sense. Sorry, I'm struggling, been at this for hours and my heads spinnin.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    =SUMIFS(S2:U27,E2:E27,E3,L2:L27,"*Public*",V2:V27,"Non-Networked")

    SUMIFS sum range can only be a single column - you have included 2 column. Try ...
    =SUMIFS(S2:s27,E2:E27,E3,L2:L27,"*Public*",V2:V27,"Non-Networked")

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    Also just noticed you have Non-Netork instead of Non-NetWork

  10. #10
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Re: Sumproduct with multiple criteria and partial text match help

    Thank you so much! That's exactly what I needed. You're a life... and time saver

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    This seems to work for you...
    =SUMPRODUCT(($E$2:$E$27="a")*(LEFT(L2:L27,6)="public")*(V2:V27="Non-Netork")*S2:U27)

  12. #12
    Registered User
    Join Date
    07-10-2021
    Location
    Texas
    MS-Off Ver
    16.41
    Posts
    6

    Re: Sumproduct with multiple criteria and partial text match help

    Yeah, I just realized that, so thank you for the second formula. I truly appreciate all of your help and quick responses. I will definitely be needing more help in the future.

  13. #13
    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,442

    Re: Sumproduct with multiple criteria and partial text match help

    If your version of Excel is for Mac, please add that detail to your forum profile.

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    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.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Sumproduct with multiple criteria and partial text match help

    Happy to help and thanks for the feedback

+ 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. [SOLVED] Find latest date with INDEX MATCH with multiple criteria & partial text
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-20-2019, 01:52 PM
  2. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  3. Sumproduct - Multiple Criteria and Partial months
    By dluhut in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-18-2014, 02:02 PM
  4. Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text
    By Groovicles in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 08-19-2013, 11:11 AM
  5. [SOLVED] Sumproduct with multiple criteria including partial search
    By Groovicles in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2013, 01:30 PM
  6. Excel 2007 : Partial Match, Multiple Criteria PLEASE HELP!!!
    By Dan.carpe@gmail.com in forum Excel General
    Replies: 5
    Last Post: 02-14-2012, 04:07 PM

Tags for this Thread

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