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 trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
I think i understand. However, will A1:A20>4 also return values that are
greater than 7? And A1:A20<7 return values that are less than 4? I'd
better explain more what I'm doing:
on let's say C1:C20 I have numerical values that represent the number of
days our clients have stayed within our facility. I converted these into
months and have the data on D1:D20, so that some have only stayed for
'0' months, others, 3, 7, 14, 9, etc... I'm trying to come up with a
report that will show how many of our clients have been staying for a
particular range of months. I would like to show in particular:
1. 3 months and under
2. 4 - 8 months
3. 9 months and above
I actually have the figures I want to report, as we are after those that
have been staying 9 months and up (i used COUNTIF(D1:D20; ">=9"), but as
a way to learn more about functions and spreadsheets, i am troubling
myself (and you guys.. thank you...) with the formula to "count" how
many in D1:D20 are between 4-8 months.
I am at home right now, but I will check out the formulas you guys sent
me tomorrow, and will post back on the result.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
