excel tip incomplete
 

This example was recently sent out and shows how to count in a
interesting way. It involves the use of an arrau formula - something
know only in passing, but it doesn't explain how the count reference
the cells it is trying to count over. In the example it needs t
count over the cells b2-b11 and c2-c11 , it checks for two condition
and gives a total of the times either of the conditions is met. It al
makes sense but doesn't work because it doesn't show how you referenc
the cells in the formula.

To count the number of rows that must satisfy criteria from tw
columns:

Insert the following Array Formula (see how to insert an Array Formul
in page 2 of this Tip):
{=SUM((Market="USA")*(Customer_Name="ExcelTip"))}

The result of the calculation is 2. The * symbol in the Array Formul
returns a result equal to the AND operator

--
rs
-----------------------------------------------------------------------
rss's Profile: http://www.excelforum.com/member.php...fo&userid=2452
View this thread: http://www.excelforum.com/showthread.php?threadid=55456

excel tip incomplete
 

There are a few unexplained magical tricks here indeed.
First: to create an array function insert it with CTRL+SHIFT+ENTER
instead of just ENTER. Excel now puts the formula between {} to signify
an array function.

Second: It is not very clear that there are named ranges used here. In
fact:
{=SUM((c2:c11="USA")*(b2:b11="ExcelTip"))}
will work just as well.

Third:
Now tricky is the following:
{=SUM((c2:c11="USA")} will yield 0
Why? Because it is evaluated to boolean values (TRUE or FALSE) and
those can't be added. So not an error is generated, but the function
evaluates to 0. Why? I don't know. Developers preference probably.

But now try:
{=SUM((c2:c11="USA") * 1} and we get 3. Magic! Why?
Because of " * 1 " the boolean is converted to an integer, which
yields 1 for TRUE and 0 for FALSE, like in VBA. Officially FALSE is 0
and any other value yields TRUE. So here is made use of this specific
(BASIC) definition of FALSE as 1, which is pretty dirty in fact.
So if the operator * (multiply) is used, booleans are converted to
numbers 0 or 1 and so we get 0*0 0*1 1*0 or 1*1, so a 1 only if both
conditions are met and so they can be summed.

The proper way to formulate would have been:
{=SUM(IF(c2:c11="USA",1,0)*IF(b2:b11="ExcelTip",1, 0))}

I hope I made things more clear.

Sincerely,

Lex


rss wrote:
This example was recently sent out and shows how to count in an
interesting way. It involves the use of an arrau formula - something I
know only in passing, but it doesn't explain how the count references
the cells it is trying to count over. In the example it needs to
count over the cells b2-b11 and c2-c11 , it checks for two conditions
and gives a total of the times either of the conditions is met. It all
makes sense but doesn't work because it doesn't show how you reference
the cells in the formula.

To count the number of rows that must satisfy criteria from two
columns:

Insert the following Array Formula (see how to insert an Array Formula
in page 2 of this Tip):
{=SUM((Market="USA")*(Customer_Name="ExcelTip"))}

The result of the calculation is 2. The * symbol in the Array Formula
returns a result equal to the AND operator.


--
rss
------------------------------------------------------------------------
rss's Profile: http://www.excelforum.com/member.php...o&userid=24524
View this thread: http://www.excelforum.com/showthread...hreadid=554568