Excell - COUNTIF

[blazblaz]

I have a different values for some measured dimensions entered in column A. (assume Total 5200 records)
I want to make some counting , based on range of the mesurements: Like
A) L<1 = 1,200 records
B) 1<L<5 = 2,500 records
C) 5<L<10 = 1,300 records
D) L>10 = 200 records
TOTAL 5,200 records
I want to know how many records have each group.(IN above sample I already determined that)

On the end of the sheet I use a fields, where I am making a summary report, entering how many records I have in each group , using COUNTIF Function


So I am using:
1)COUNTIF( A1:A5200),”<1”)
2)COUNTIF(A1:A5200),”>1)
Etc….

In my next report I have different number of records (like 10,230), so I have to change the range of the field instead (A1:A5200) will be (A1:A10230) . I would like not to change the formulas every time the number of records are changed. I’d like to use a new cell, where I can enter how many records I have in my report, and that value would be automatically read through some macro in my COUNTIF() function.
Is it possible to do that in Excell?

 

[macropod]

Hi,

Is there a problem with simply specifying the whole column? For example:
=COUNTIF(A:A),"<1")

If for some reason you need to limit the range, you could use:
=COUNTIF(A1:OFFSET(A1,C1,0),">1")
with the last row number in C1 to specify the range limit.

Cheers

 

[KenWright]

The following will do what you want assuming that you have no blanks in your data range in Col A and that there is no other data in Col A below your data range. the OFFSET piece makes the ranmge dynamic so you can add mnore data to your range and not have to change the formulas.

=COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),"<1")

=COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),"<5")-COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),"<1")

=COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),"<10")-COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),"<5")

=COUNTIF(OFFSET($A$1,,,COUNTA(A:A)),">10")

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------