Using AND criteria in an array

[Stefan Keydel]

I'm trying to use an array formula from one of John Walkenbach's books
to calculate the number of occurences of a specific combination in two
ranges of data. Let's say that the columns are as follows:

Store Day

Best Buy Monday
Circuit City Monday
Best Buy Tuesday
Best Buy Monday
Circuit City Tuesday

and I want to calculate the number of occurences of the "Best Buy,
Monday" combination. According to Walkenbach, I should able to enter the
following array formula to get this result:

{=SUM((Store="Best Buy")*(Day="Monday"))}

So, I'm entering this array into an adjacent empty cell, using
Apple-Return to get the curly braces, but the result I get is 0. Can
someone explain why this isn't working for me?

Thanks!

Stefan

 

[Bob Greenblatt]

I'm trying to use an array formula from one of John Walkenbach's books
to calculate the number of occurences of a specific combination in two
ranges of data. Let's say that the columns are as follows:

Store Day

Best Buy Monday
Circuit City Monday
Best Buy Tuesday
Best Buy Monday
Circuit City Tuesday

and I want to calculate the number of occurences of the "Best Buy,
Monday" combination. According to Walkenbach, I should able to enter the
following array formula to get this result:

{=SUM((Store="Best Buy")*(Day="Monday"))}

So, I'm entering this array into an adjacent empty cell, using
Apple-Return to get the curly braces, but the result I get is 0. Can
someone explain why this isn't working for me?

Thanks!

Stefan

Do you have the names for Store and day defined to be the ranges you are
looking at? You might try changing the formula to something like:
=sum(sum(a2:a10="Best Buy")*(b2:b10="Monday"))
Array entered of course.

 

[Stefan Keydel]

Do you have the names for Store and day defined to be the ranges you are
looking at? You might try changing the formula to something like:
=sum(sum(a2:a10="Best Buy")*(b2:b10="Monday"))
Array entered of course.

Hi Bob,

Yes, I do have the ranges defined. However, I should say that my example
was not quite accurate, and if I actually use the values I provided in
my post, it *does* work. I apologize for creating confusion.

Here's the kicker: In the actual spreadsheet in which I'm working, one
of the ranges is populated with numbers. So, using the example I
provided above, but changing the day of the week to a number like "6", I
get the incorrect answer as listed above ("0"). Is there a restriction
on one of the criteria being a number?

Thanks again!

Stefan

 

[JE McGimpsey]

Yes, I do have the ranges defined. However, I should say that my example
was not quite accurate, and if I actually use the values I provided in
my post, it *does* work. I apologize for creating confusion.

Here's the kicker: In the actual spreadsheet in which I'm working, one
of the ranges is populated with numbers. So, using the example I
provided above, but changing the day of the week to a number like "6", I
get the incorrect answer as listed above ("0"). Is there a restriction
on one of the criteria being a number?

There's no restriction, but the criterion and the values in the range
must have the same format - i.e., if your values are numbers, the
criterion should be a number:

{=SUM((Store="Best Buy")*(Day=1))}

but if the values are text, you need your criterion to be text:

{=SUM((Store="Best Buy")*(Day="1"))}

BTW: SUMPRODUCT is considerably faster than array-entered SUM(IF()) (and
it doesn't need to be array-entered):

=SUMPRODUCT((Store="Best Buy")*(Day=1))

or even a bit faster:

=SUMPRODUCT(--(Store="Best Buy"),--(Day=1))

Note that SUMPRODUCT requires numeric arrays (rather than Boolean
TRUE/FALSE arrays produced by (Day=1), for example). Using the *
operator implicitly coerces the arrays of TRUE/FALSE to 1/0. So does the
- operator (to -1/0, which is why the second is needed). The second form
is slightly faster because SUMPRODUCT's array multiplication is a bit
faster than doing the array-multiplication first, then applying
SUMPRODUCT to sum the results.

 

[Stefan Keydel]

There's no restriction, but the criterion and the values in the range
must have the same format - i.e., if your values are numbers, the
criterion should be a number:

{=SUM((Store="Best Buy")*(Day=1))}

but if the values are text, you need your criterion to be text:

{=SUM((Store="Best Buy")*(Day="1"))}

BTW: SUMPRODUCT is considerably faster than array-entered SUM(IF()) (and
it doesn't need to be array-entered):

=SUMPRODUCT((Store="Best Buy")*(Day=1))

or even a bit faster:

=SUMPRODUCT(--(Store="Best Buy"),--(Day=1))

Note that SUMPRODUCT requires numeric arrays (rather than Boolean
TRUE/FALSE arrays produced by (Day=1), for example). Using the *
operator implicitly coerces the arrays of TRUE/FALSE to 1/0. So does the
- operator (to -1/0, which is why the second is needed). The second form
is slightly faster because SUMPRODUCT's array multiplication is a bit
faster than doing the array-multiplication first, then applying
SUMPRODUCT to sum the results.

Perfect. Thank you, sir!

Stefan