determine number of rows in a dataset (Excell 2003)

Topic

New Reply

Hi,

I know there is a formula for this but I can’t for the life of me remember it or its name. I import data into a worksheet. I want to put a forumula in another worksheet which will countif …. a value occurs in the dataset, and the number of rows in the data set might vary…. can anyone help

Reply | Quote

Replies

If you are trying to count things on Sheet1 for the value of 47:

=COUNTIF(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA($1:$1)),47)

It presumes that the dataset starts in A1 and that the dataset is continuous in row1 and in column A (neither have blanks within the dataset).

Steve

Reply | Quote

Brilliant thanks !!

Reply | Quote

Count dataset for the value of 47:

Please try this formula,

the formula can work in non-continuous ranges and blank cells :

=COUNTIF(OFFSET($A$1,0,0,MATCH(9.99E+307,$A:$A),MATCH(9.99E+307,$1:$1)),47)

Regards
Bosco

Reply | Quote

Note: This may not work correctly if there are text values in Column A or in row 1…

Steve

Reply | Quote