At 22:22 26/02/2009 -0700, Girard Aquino wrote:
Brian Barker wrote:
At 21:37 25/02/2009 -0700, Girard Aquino wrote:
Hi! I have a calc question. on one column i have a bunch of even
numbers. I would like to "count" how many are less than or equal
to 3, greater than 4 but less than 7, or greater than or equal to
9. the first and last conditions i got through the COUNTIF (), my
problem is defining the condition for the second one. What
function can i use for this?
One way to do this is to use the SUMPRODUCT() function. Try something like:
=SUMPRODUCT(A1:A20>4;A1:A20<7)
- where A1:A20 is the range of cells to be counted.
How does this work? The expression A1:A20>4 creates a array of
logical values, TRUE or FALSE according to whether the condition is
satisfied. The second expression produces a second array according
to the other condition. Now the SUMPRODUCT() function expects
numerical arguments, so the logical values it is given are
interpreted as 1 (for TRUE) or 0 (for FALSE). The function then
sums the products of corresponding values in the two arrays, and
these products will be 1 if both conditions are satisfied and 0 otherwise.
Note that, although this formula handles arrays of values, it is
not an array formula and you use normal Enter to complete it, not
Ctrl+Shift+Enter.
I think i understand. However, will A1:A20>4 also return values that
are greater than 7?
Yes.
And A1:A20<7 return values that are less than 4?
Yes. That's why neither expression would work - and neither was
suggested. Instead, a combination of both using SUMPRODUCT() was
suggested - which works!
I'd better explain more what I'm doing: ...
No need: you were perfectly clear first time around!
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
