Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I modify a SUMPRODUCT formula with an OR statement?

Posted on 2011-09-07
5
Medium Priority
?
291 Views
Last Modified: 2012-05-12
Hello experts,

I want to modify this formula:
[=SUMPRODUCT((E$5:E$76)*($B$5:$B$76="KC TRNG"))
to capture 3 different conditions by including the OR function:
[=SUMPRODUCT((E$5:E$76)*($B$5:$B$76=OR("KC TRNG","X-TRNG","LOG-TRNG")))

Not working.  What should I do?

Gary
0
Comment
Question by:garyrobbins
  • 2
  • 2
5 Comments
 
LVL 7

Assisted Solution

by:BusyMama
BusyMama earned 400 total points
ID: 36496673
That's not working because the OR statement returns a True/False (1 or 0).

I would do it this way, although there is probably something fancier out there.

=SUMPRODUCT((E$5:E$76)*(($B$5:$B$76="KC TRNG")+($B$5:$B$76="X-TRNG")+($B$5:$B$76="LOG-TRNG")))
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1600 total points
ID: 36496674
Hello Gary, try this version

=SUMPRODUCT((E$5:E$76)*($B$5:$B$76={"KC TRNG","X-TRNG","LOG-TRNG"}))

...or you can use SUMIF, i.e.

=SUM(SUMIF($B$5:$B$76,{"KC TRNG","X-TRNG","LOG-TRNG"},E$5:E$76))

or if you actually want to SUM whenever the B column value ends with "TRNG" you could use this version with a "wildcard"

=SUMIF($B$5:$B$76,"*TRNG",E$5:E$76)

regards, barry
0
 

Author Comment

by:garyrobbins
ID: 36497596
Ok, thanks for the exceptional response time!

So, can I have an expanation of the braces {} in this formula:
=SUMPRODUCT((E$5:E$76)*($B$5:$B$76={"KC TRNG","X-TRNG","LOG-TRNG"}))

And, if the wildcard works in this formula:
=SUMIF($B$5:$B$76,"*TRNG",E$5:E$76)
why would it not work in this form?
=SUMPRODUCT((E$5:E$76)*($B$5:$B$76="*TRNG"))

Thanks,
Gary
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 1600 total points
ID: 36497779
Hello Gary

The part in the braces, i.e. {"KC TRNG","X-TRNG","LOG-TRNG"}, is known as an "array constant". If you were to compare that to one cell then you get a similar array as the result - e.g. if you used

=A1={"KC TRNG","X-TRNG","LOG-TRNG"}

that would give a result something like this

{TRUE,FALSE,FALSE}

(assuming A1=KC TRNG)

when you compare the array constant to a column like in your formula you get an array which is three columns wide (because there are three elements in the array constant) and as long as the comparison column.

When you multiply that array by E5:E76 then all TRUEs perform like 1s and FALSEs like zeroes so the effect is to get three zeroes for each row with no matches.......or two zeroes and the column E value for each row with a match.

You may read in some places that wildcards "don't work" in SUMPRODUCT - that's not strictly true. It's the direct comparison that doesn't work, regardless of SUMPRODUCT. If you put this formula in a cell

=A1="*TRNG"

then in that formula * isn't treated as a wildcard but as a literal "*"....so that formula will only be TRUE if A1 actually contains that exact text (including asterisk). Wildcards aren't compatible with "comparison operators" like =, > and <.

To make that work in SUMPRODUCT you could use RIGHT function, i.e.

=SUMPRODUCT((E$5:E$76)*(RIGHT($B$5:$B$76,4)="TRNG"))

[If "TRNG" was always at the start of the string, you'd use LEFT instead - anywhere in the string you need to use SEARCH or FIND]

BusyMama's approach is just as valid - although it tends to lengthen the formula a little if you have multiple strings for comparison.......

regards, barry
0
 

Author Closing Comment

by:garyrobbins
ID: 36498088
Thank you, Barry.  You really know this stuff!  Thank you for taking time to offer the explanations.

Since BusyMama's answer worked also (though not the one I used) she did come in a split second ahead -- I'm awarding her some points.

Hope you think this is fair.

Thank you all -- Expert Exchange ROCKS.

Gary
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question