What does this mean?

In the formula below, what does the asterisk '*' mean?

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))


Many thanks

It serves as an "AND" condition. In the formula,

(A2:A500="5/27/2005")
and
(E2:E500="5098")

return arrays of TRUE or FALSE values, each indicating the result
of the comparison. So, for example, you'll get two arrays like

{TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}

The multiplication operator * multiplies these two arrays
together; each element in the first array is multiplied by the
corresponding element in the second array, and the result is an
array of these products.

Since Excel treats TRUE as 1 and FALSE as 0, the resulting
product of the multiplication will be 1 (or TRUE) only when both
operands are TRUE (or 1).

So, using the example arrays above, the result of the
multiplication is

(0, 1, 0, ...)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Andrew Chalk" <[email protected]> wrote in
message news:[email protected]...
> In the formula below, what does the asterisk '*' mean?
>
> =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>
>
> Many thanks
>
>

Is it an array function? (Are there braces around it?) If so, it means kinda
like an additional condition follows...
example

Col

Row C D
5 Bill 10
6 Tom 50
7 Bill 100
110 {=SUM((C5:C7="Bill")*(D5:D7))}


shows the sum of items in column D (the cells indicated) when items in
Column C (the cells indicated) are = value "Bill"

You have to enter a formula like this using Ctrl-Shift-Enter, not just
Enter.

HTH,

Bill

"Andrew Chalk" <[email protected]> wrote in message
news:[email protected]...
> In the formula below, what does the asterisk '*' mean?
>
> =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>
>
> Many thanks
>
>

"Andrew Chalk" <[email protected]> wrote...
>In the formula below, what does the asterisk '*' mean?
>
>=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

It means a logical AND that would give a formula result. Excel's AND
function returns only a single result, so

AND({FALSE;FALSE;TRUE;TRUE},{FALSE;TRUE;FALSE;TRUE})

returns FALSE rather than {FALSE;FALSE;FALSE;TRUE} (the pairwise AND result
for the two arrays). The standard way of doing a pairwise AND involves using
arithmetic multiplication. TRUE is converted to 1 and FALSE to 0, so

{FALSE;FALSE;TRUE;TRUE}*{FALSE;TRUE;FALSE;TRUE}

becomes

{0;0;1;1}*{0;1;0;1}

which evaluates to {0;0;0;1}, and when that result is used as the first
argument to IF, 0 is treated as FALSE and nonzero (so 1) as TRUE.

OK. A cross-product in matrix parlance?

Thanks for the detail. Same goes for QWilliam and Harlan's replies.

- Andrew
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> It serves as an "AND" condition. In the formula,
>
> (A2:A500="5/27/2005")
> and
> (E2:E500="5098")
>
> return arrays of TRUE or FALSE values, each indicating the result
> of the comparison. So, for example, you'll get two arrays like
>
> {TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}
>
> The multiplication operator * multiplies these two arrays
> together; each element in the first array is multiplied by the
> corresponding element in the second array, and the result is an
> array of these products.
>
> Since Excel treats TRUE as 1 and FALSE as 0, the resulting
> product of the multiplication will be 1 (or TRUE) only when both
> operands are TRUE (or 1).
>
> So, using the example arrays above, the result of the
> multiplication is
>
> (0, 1, 0, ...)
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Andrew Chalk" <[email protected]> wrote in
> message news:[email protected]...
> > In the formula below, what does the asterisk '*' mean?
> >
> > =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
> >
> >
> > Many thanks
> >
> >
>
>

Interesting analogy, I guess it may be a "special case" of a matrix product
.... however, whereas matric arithmetic doesn't care if the two arrays (or
vectors) are of the same size, Excel does (at least in this case, so far as
I know). It fails if each array does not have the same number of elements.


"Andrew Chalk" <[email protected]> wrote in message
news:[email protected]...
> OK. A cross-product in matrix parlance?
>
> Thanks for the detail. Same goes for QWilliam and Harlan's replies.
>
> - Andrew
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> It serves as an "AND" condition. In the formula,
>>
>> (A2:A500="5/27/2005")
>> and
>> (E2:E500="5098")
>>
>> return arrays of TRUE or FALSE values, each indicating the result
>> of the comparison. So, for example, you'll get two arrays like
>>
>> {TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}
>>
>> The multiplication operator * multiplies these two arrays
>> together; each element in the first array is multiplied by the
>> corresponding element in the second array, and the result is an
>> array of these products.
>>
>> Since Excel treats TRUE as 1 and FALSE as 0, the resulting
>> product of the multiplication will be 1 (or TRUE) only when both
>> operands are TRUE (or 1).
>>
>> So, using the example arrays above, the result of the
>> multiplication is
>>
>> (0, 1, 0, ...)
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Andrew Chalk" <[email protected]> wrote in
>> message news:[email protected]...
>> > In the formula below, what does the asterisk '*' mean?
>> >
>> > =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>> >
>> >
>> > Many thanks
>> >
>> >
>>
>>
>
>

As a matter of curiosity what is the difference between

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

=COUNT(IF(AND(A2:A500="5/27/2005",E2:E500="5098"),E2:E500))
(assuming you can have count/if/and)

Regards.

Bill Ridgeway
Computer Solutions

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> It serves as an "AND" condition. In the formula,
>
> (A2:A500="5/27/2005")
> and
> (E2:E500="5098")
>
> return arrays of TRUE or FALSE values, each indicating the result of the
> comparison. So, for example, you'll get two arrays like
>
> {TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}
>
> The multiplication operator * multiplies these two arrays together; each
> element in the first array is multiplied by the corresponding element in
> the second array, and the result is an array of these products.
>
> Since Excel treats TRUE as 1 and FALSE as 0, the resulting product of the
> multiplication will be 1 (or TRUE) only when both operands are TRUE (or
> 1).
>
> So, using the example arrays above, the result of the multiplication is
>
> (0, 1, 0, ...)
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Andrew Chalk" <[email protected]> wrote in message
> news:[email protected]...
>> In the formula below, what does the asterisk '*' mean?
>>
>> =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>>
>>
>> Many thanks
>>
>>
>
>

Apples and oranges... Array multiplication versus simply evaluating a
single, condition which just
happens to involve arrays.

If all items in A2:A500 do in fact = "5/27/2005", AND
all items in E2:E500 do in fact = "5098", your answer will be = 499
(provided you enter the formula as an array -- otherwise you get an error).

If EITHER not all items in A2:A500 = "5/27/2005", OR
not all items in E2:E500 = "5098", you get COUNT(FALSE) ... which for
reasons which I do not know, resolves to 1.

Why the latter puzzles me is because I know that counting an
array of numericals & logicals, whether entered as a regular or an array
formula will result in a count of only the numbers

yet this function count(FALSE) resolves to a value of 1 !!!


If you try it with SUM() instead of COUNT() you will get zero



"Bill Ridgeway" <[email protected]> wrote in message
news:[email protected]...
> As a matter of curiosity what is the difference between
>
> =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>
> =COUNT(IF(AND(A2:A500="5/27/2005",E2:E500="5098"),E2:E500))
> (assuming you can have count/if/and)
>
> Regards.
>
> Bill Ridgeway
> Computer Solutions
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> It serves as an "AND" condition. In the formula,
>>
>> (A2:A500="5/27/2005")
>> and
>> (E2:E500="5098")
>>
>> return arrays of TRUE or FALSE values, each indicating the result of the
>> comparison. So, for example, you'll get two arrays like
>>
>> {TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}
>>
>> The multiplication operator * multiplies these two arrays together; each
>> element in the first array is multiplied by the corresponding element in
>> the second array, and the result is an array of these products.
>>
>> Since Excel treats TRUE as 1 and FALSE as 0, the resulting product of the
>> multiplication will be 1 (or TRUE) only when both operands are TRUE (or
>> 1).
>>
>> So, using the example arrays above, the result of the multiplication is
>>
>> (0, 1, 0, ...)
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Andrew Chalk" <[email protected]> wrote in message
>> news:[email protected]...
>>> In the formula below, what does the asterisk '*' mean?
>>>
>>> =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))
>>>
>>>
>>> Many thanks
>>>
>>>
>>
>>
>
>