Sumproduct with OR criteria, unable to properly subtract all the AND cases.

I have a spreadsheet where I have used SUMPRODUCT formulas to create a number of dynamic tables. I received a request recently to update the tables to add a couple of additional filters that can be selected by the user. The filters need to allow for selecting a single value or all of the values. My solution to that problem was to include an if statement combined with an OR condition in the SUMPRODUCT formula via the use of a +. So for example my formula would look like this:

Please Login or Register  to view this content.

After implementing that I found out very quickly that this was not enough and that I needed to also subtract out from the above any AND conditions to arrive at a true result. This is the thread that helped me figure out how to this: Thread Link.

So the final formula looks like this:

Please Login or Register  to view this content.

This worked wonderfully. However, when I attempted to add a second filter criteria the formula would no longer work as the AND subtraction would not subtract the proper amounts any longer.

I’ve attached a file which shows an example of what I’m trying to achieve including the data table, the expected results, and my versions of the formulas.

Sumproduct Example.xls

Bear in mind that my actual formulas are much more complex and include a half dozen other criteria, but I decided to keep these as simple as possible and focus only on the part that I’m unable to figure out.

I hope someone can help!

dredwolf, I don't think you understood my issue. But thanks for the effort

Dredwolf, did you look at the file? I have no issue adding additional criteria. The issue is that these OR conditions in a sumproduct cause something akin to a Cartesian product and as a result you have to back out the duplicates. This concept is briefly mentioned in this post by rwgrietveld.

If you open the file you will see that changing the selections in cells C2 and C3 will cause a different cell in the “Expected Results” section to be highlighted. The highlighted cell represents the expected output of the formula in cell C11. As you can see, I’m unable to get the formula to match the expected result and this is what I need help with.

I hope this makes it a bit clearer as to what I’m trying to achieve.

Thanks,
Psy

Originally Posted by vlady

i noticed that the sumproduct is continuous maybe that's the issue if you can give the expected output on that sample file (not altering it)
Criteria 1 =A
Criteria2 =both
=sumproduct((..)+(..)+(..)*(...)+(..)+(..)

that is --> sumproduct(or,or,and,or,or)

can you try to segregate the or/andand statement with open close parent ()
=sumproduct((...)+((...))*((...)+(...))

that is -->sumproduct((or,or,or) and (or,or,or))

Vlady, you have gotten me much closer! With your suggestion I was able to modify the formula so that I get the correct response for any case involving at least one of the criteria being equal to "Both". I added the extra segregations and also one OR condition to the negative (subtracted) sumproduct formula.

However, the new formula didn't work if neither selection was "Both". So then I just added an IF statement at the start to see if either is both, and if it's not then use a different sumproduct formula.

The sheet I attached was a sample, but here is my actual working formula from my actual worksheet. =IF(OR($D$1="All",$D$2="All"),SUMPRODUCT(((((WLD!$F$2:$F$7000=IF($D$1="All","New",$D$1))+(WLD!$F$2:$F$7000=IF($D$1="All","Existing",$D$1)))*((WLD!$G$2:$G$7000=IF($D$2="All","Development",$D$2))+(WLD!$G$2:$G$7000=IF($D$2="All","Commercial",$D$2)))))*((WLD!$A$2:$A$7000=$B8)*(WLD!$H$2:$H$7000=1)*(WLD!$D$2:$D$7000>=C$18)*(WLD!$D$2:$D$7000<=C$19)*(WLD!$K$2:$K$7000)))-SUMPRODUCT(((((WLD!$F$2:$F$7000=IF($D$1="All","New",$D$1))*(WLD!$F$2:$F$7000=IF($D$1="All","Existing",$D$1)))+((WLD!$G$2:$G$7000=IF($D$2="All","Development",$D$2))*(WLD!$G$2:$G$7000=IF($D$2="All","Commercial",$D$2)))))*((WLD!$A$2:$A$7000=$B8)*(WLD!$H$2:$H$7000=1)*(WLD!$D$2:$D$7000>=C$18)*(WLD!$D$2:$D$7000<=C$19)*(WLD!$K$2:$K$7000))),SUMPRODUCT((WLD!$G$2:$G$7000=$D$2)*(WLD!$F$2:$F$7000=$D$1)*(WLD!$A$2:$A$7000=$B8)*(WLD!$H$2:$H$7000=1)*(WLD!$D$2:$D$7000>=C$6)*(WLD!$D$2:$D$7000<=C$7)*(WLD!$K$2:$K$7000)))

Since I'm on excel '03, I had to actually rename one of my tabs or else the formula was too long to fit!

Thank you so much Vlady!!!

-Psy

Originally Posted by tigeravatar

Psy,

Attached is v2. I included 2 different formulas that provide the correct results. Either could be in cell C11.

In C11 is this formula (which I came up with first):

Please Login or Register  to view this content.

In C12 is this formula:

Please Login or Register  to view this content.

Would either of those work for you?

Hi Tiger,

Unfortunately what I left out of my original worksheet is that I'm combining this with a number of other criteria (and always using a value multiplier) so a simple count would not suffice.

Am I correct in assuming that the above formulas would only work with counts and not with a value multiplier like sumproduct does?

I've applied the aforementioned approach the sample sheet as well so that everyone can see what the solution looks like based on the help they gave me. If anyone wants to suggest an simpler way of getting at the same result, that would be great, but at this point it's working so that's good enough!

Thanks again everyone!!!

In the sheet attached I added a couple of new sections which show a static criteria being added to the formula in addition to the two dynamic linked criteria.

Sumproduct Example 2.xls

Originally Posted by tigeravatar

Sumproduct used in this manner is an Excel 2003 formula for counting because Countifs wasn't available yet. In both formulas, the Countif formula could be replaced with a sumproduct to do the same thing. The second formula is entirely value multipliers, and again you could replace each Countif with Sumproduct to do the same thing.

I see. Your formulas seem to be a lot simpler than mine. I'm going to try to use your approach instead of what I went with to see if I can make it work. I'll report back if I can figure it out (and probably if I can't as well).

Thanks!

Originally Posted by daddylonglegs

I think you may be over-complicating things, this works for all combinations on your original attachment

=SUMPRODUCT((H4:H13=IF(C2="Both",H4:H13,C2))*(I4:I13=IF(C3="Both",I4:I13,C3)))

I didn't know you could put an array as a result of an IF statement!! Oh man, I thought about that at first and then just assumed it wouldn't work.

I'm going to try to apply that to my actual worksheet. Thank you!!!

Yup, it works wonders! Thanks again daddylonglegs!!! and thanks to everyone else as well.