Sum Multiple Criteria

[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!

Also, can anyone recommend a book to learn such advanced formulas?

 

[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

RP
(remove nothere from the email address if mailing direct)

 

[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