|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
Countif
--
LarryJasper
"Rick Rothstein (MVP - VB)" wrote:
=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))
Why would you Multiply these 2 numbers?? I want just a count meeting
both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both
condition must be met.
That is one of the syntaxes for the SUMPRODUCT function. What is happening
is the first expression is multiplied by the second for each element in the
range and then those individual products are summed up (hence the name
SUMPRODUCT). Now look at the expressions... they are each logical
expressions which evaluate to TRUE or FALSE. (Excel automatically converts
TRUE to 1 and FALSE to 0 when used in a mathematical computation.) So, if
both conditions are met, you have a product of 1 and if any one condition is
not met, you have a product of 0. That means the sum will only increment by
1 for each product where both conditions are met. The end result of that...
the count that you asked for.
The other way I could have written the formula is this way....
=SUMPRODUCT(--(Data!F2:F600=DOS1),--(Data!E2:E600<1))
but now you have to convert the TRUE and FALSE results to numbers manually.
This is done with the double unary symbols... my personal preference is to
avoid the double unary symbol if I can (hence, why I chose the
multiplication form of the formula over the comma form).
Rick
Thank for the lesson. and the formula. It works.
|