Sumproduct with multiple criteria and partial text match help

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

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

3. 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. 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. Re: Sumproduct with multiple criteria and partial text match help

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

6. 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. 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. 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. Re: Sumproduct with multiple criteria and partial text match help

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

10. 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. 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. 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. Re: Sumproduct with multiple criteria and partial text match help

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.

14. Re: Sumproduct with multiple criteria and partial text match help

Happy to help and thanks for the feedback

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