Question to Bob Phillips (or whoever...)

[vezerid]

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 ====

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!

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

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

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

 

[Bob Phillips]

Hi Kostis,


I am not sure exactly what it is that you don't understand, but I will take
a shot at it.

You seem to be asking why I can include an embedded array within my
SUMPRODUCT formula. As you say, the formula

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

ignores the array, and behaves the same as

=IF(J3="A",1,0)

You can also show that if you enter say A,B,C in A1:A3, a formula of

=IF(J3=A1:A3,1,0)

will also ignore the array and behave the same as

=IF(J3=A1,1, 0)

This is because If is not an array function, and normally expects a single
cell reference, or a value.

However, there are some functions that work specifically on arrays, such as
VLOOKUP. A formula like this

=VLOOKUP(lookup_value,lookup_table,2,False)

will return the value in the 2nd column of the row where lookup_value
matches. lookup_table is a range, as in this example

=VLOOKUP("ABC",M1:O100,2,False)

but that range can be replaced by an embedded array, like this

=VLOOKUP("XYZ",{"ABC","A1","B1";"DEF","A2","B2";"XYZ","A99","B99"},2,False)

So, you can see that we can embed arrays into an array function, and it
works. SUMPRODUCT is an array function, and so you can also embed arrays in
there.


--

HTH

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

 

[Bruno Campanini]

[...]

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)

Not exactly!

1 - Enter in B22 =IF(A2={"A","B","C"},1,0)
2 - Enter in A3:A5 =IF(A2={"A";"B";"C"},1,0)
Both FormulaArray over the range (B22 and A3:A5)
Please note the difference between the two formulas "," ";"
The first "," is used to separate "fields" (columns),
the second to separate "records" (rows).

Type A, B, C in cell A2 and see what happens.
More, when you are in one range push F2 then F9
to see the proper array.

If you want to avoid =IF(OR(... you can
write =SUM(IF(A2={"A", "B", "C"}, 1, 0))
or =SUM(IF(A2={"A"; "B"; "C"}, 1, 0))

Or =(BU39="A")+(BU39="B")+(BU39="C")

No need at all to use, under this circumnstance, SUMPRODUCT():

Ciao
Bruno

 

[Ragdyer]

You can use this:

=IF(OR(J3={"A","B","C"}),1,0)

Which works fine!

Now ... no one has mentioned here exactly what
{"A","B","C"}
actually is.
It's *not* an array to XL, it's an *array constant*,
which means it takes the *place* of an array!

What Bob used in the Sumproduct formula is an array constant,
As is:
=LOOKUP(A1,{1,2,3,4,5;"A","B","C","D","E"})
AND
=INDEX(A1:A5,MATCH(B2,{1,2,3,4,5},0))

From the help files:
Array Constants *cannot* contain:
$ signs
% signs
Cell references
Parenthesis
Columns or rows of unequal length

Look up the term in the help files for further information.

 

[vezerid]

Hi all,
thank you for your replies. First, I must apologize for throwing the
post and leaving shortly afterwards, but it was late here and the guard
was pressing me to leave office. I guess I should have waited for
today, to be able to follow up the thread.

My question remains unanswered, but I guess it is me to blame. Instead
of pasting past messages I should write a concise message with all the
examples and what exactly puzzles me. Unfortunately for me I cannot do
this today, so I will come up with another post soon.

Thanks again,
Kostis Vezerides

 

[Aladin Akyurek]

Try:

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