Hi Bob,

There was a thread yesterday in which we both posted solutions. After
posting my answer I saw yours and it ticked me because there are some
things I could not understand. So I posted a followup question but
apparently you did not visit the thread.

I am appending the necessary text here for you to remember the problem
and see my reasoning. If you have the time can you please answer my
question?

==== Appended text ====

Rhiannons_Wish

I'm trying to sum with multiple criteria. I've been reading other
postings
and tried the following with no success:

=SUMPRODUCT(--('BOM LIST'!$J$8:$J$770=B31),--('BOM
LIST'!$A$8:$A$770={"S","S-FED","O"}),--('BOM
LIST'!$G$8:$G$770={"F","Q","R"}),'BOM LIST'!$Z$8:$Z$770)

Help!

Bob Phillips

Try

=SUMPRODUCT(('BOM LIST'!$J$8:$J$770=B31)*
('BOM LIST'!$A$8:$A$770={"S","S-FED","O"})*
('BOM LIST'!$G$8:$G$770={"F","Q","R"}),'BOM LIST'!$Z$8:$Z$770)

--

HTH

vezerid

Your problem is most likely due to using equality with an array when
you really want inclusion in a set. Although I have seen this construct

working, I only recently found out about it here in the newsgroups and
I have not yet clarified to myself when it works and when not.


Thus, the "natural" thought that this construct might work,
=IF(A2={"A", "B", "C"}, 1, 0)
implying that we would get 1 if A2 is either "A", "B" or "C",
does not work and needs instead and OR() as in:
=IF(OR(A2="A", A2="B", A2="C"), 1, 0)


Problem is, SUMPRODUCT() accepts computed arrays in some forms, like
in:
=SUMPRODUCT(A1:A10, --(B1:B10="A")),
in which case the second array argument is a computed array of TRUE or
FALSE based on whether Bi="A" for each i in 1..10.


BUT, at least in my version, it will not accept the following:
=SUMPRODUCT(A1:A10, IF(B1:B10="A", 1, 0)),
unless it is array-entered, i.e.entered with Shift+Ctrl+Enter. In this
case we force the second argument to be treated as an array to IF() and

thus producing a computed array of 1 and 0 based on the same condition.



In most cases, the benefit of SUMPRODUCT() is that it does not require
array-entering. However, in this case this benefit is defeated since we

need array entering anyway. Given this, it would likely be simpler to
use array-SUM() instead, like in the following formula, equivalent to
the last SUMPRODUCT. Notice that essentially we replace the ","
delimiter in SUMPRODUCT with the multiplication opeerator "*", since we

are summing over a computed array, itself the pairwise product of two
arrays
=SUM(A1:A10 * IF(B1:B10="A", 1, 0))

In conclusion, regarding your specific formula:
- Replace the ={...} construct with an IF(OR(...), 1, 0)
- Array enter your modified formula.

Optionally, you might use SUM(array * array * ...), which must also be
array-entered.

HTH
Kostis Vezerides

vezerid

Hi Bob,

I was writing my own reply while you posted your answer. As I say in my

post, I have still not fully understood when ={...} works. In the post
I reflect my current understanding of this.

Can you please explain why your formula works? I tested it in my own
test data set and verified that its philosophy works. One thing I have
come to conclude myself since I wrote the post is that SUMPRODUCT,
without array entering, will accept as arguments computed arrays if
they are the result of operations. If however, the computed array is
the result of a function, then it needs array entering.

Yet, I am still puzzled by some things:

- In a column with values in {"A", "B", "C"} the following formula does

not work:
=IF(J3={"A","B"}, 1, 0)

If I simply enter it, then it produces #VALUE!.
If I array-enter it, it only recognizes the "A", consistent with the
behavior when an array is used in a formula, in a place where a scalar
is expected.
However, it obviously works in the following, same philosophy as your
formula, i.e. without array-entering:
=SUMPRODUCT(K2:K15*(J2:J15={"A","B"}))

This I cannot explain. Can you enlighten please?

Regards,
Kostis Vezerides