How to count within a data sheet?

Posted by Brian on February 02, 2002 7:41 PM

How do you say that you want all the numbers from 100-200, pulled out and counts them for you?

Thanks,
Brian

Posted by Aladin Akyurek on February 03, 2002 12:35 AM

Brian --

COUNTIF will suffice for what you want. But, first a note about the ambiguity of between X and Y or from X (to) Y as you put it.

Lets say that E1 houses the lowest and E2 the highest of criteria. Your 100 and 200, respectively.

What do we mean when we say "between E1 and E2" or from E1 to E2? It can be one of the following:

(a) > E1 and &LT; E2 [ also called Exclusive Between ]
(b) >= E1 and &LT;= E2 [ also called Inclusive Between ]
(c) > E1 and &LT;= E2
(d) >= E1 and &LT; E2

For (a), use:

=COUNTIF(A2:B200,">"&E1)-COUNTIF(A2:B200,">"&E2)

For (b), use:

=COUNTIF(A2:B200,">="&E1)-COUNTIF(A2:B200,">"&E2)

For (c), use:

=COUNTIF(A2:B200,">"&E1)-COUNTIF(A2:B200,">"&E2)

For (d), use:

=COUNTIF(A2:B200,">="&E1)-COUNTIF(A2:B200,">="&E2)

Note. The COUNTIF function also accepts whole columns as arguments to such as A:A or A:B. The columns should not have anything else (any other numbers) but the numbers of interest that we want count.

Aladin

============