"vezerid" wrote in message
ups.com...

[...]
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 B2:D2 =IF(A2={"A","B","C"},1,0)
2 - Enter in A3:A5 =IF(A2={"A";"B";"C"},1,0)
Both FormulaArray over the range (B2:D2 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





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