How do I modify a SUMPRODUCT formula with an OR statement?
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")))
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
garyrobbinsAuthor Commented:
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"))
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.
I would do it this way, although there is probably something fancier out there.
=SUMPRODUCT((E$5:E$76)*(($