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