Counting fully filled rows in a filtered matrix

[hellcat83]

Hi

I'm trying to calculate the number of rows in a matrix that have a complete row of data. I would noprmally do this using nested if formuals and a count function, but the columns of the matrix are filterable so the count won't work.

Is there a combination of functions that will adapt as I filter data?

Cheers

 

[PaddyD]

short answer - yes.
slightly longer answer - have a look here for a technique to get formulas working over filtered ranges:

http://spreadsheetpage.com/index.php/site/eee/issue_no_20_july_8_2001/

longer answer - will need you to be more specific about the data you've got, answers you need etc. a worked example would help.

 

[hellcat83]

Sorry,

An example might be;

6 5 6
4 5
8 7 8
0
5 5 5

I'm calculating the correlations for cols A&B and A&C. That works ok with gaps in.

I'm also calulating the statistical significances of those correlations and need to know how many rows of data have been used. I need something that can ignore the rows with missing numbers and provide a count of how many complete rows there are, in this case 3. The issue is these columns will be filtered to tailor the correlations, so the counting needs to be able to cope with that.

Does that shed anymore light?

Thanks

 

[T. Valko]

Sorry,

An example might be;

6 5 6
4 5
8 7 8
0
5 5 5

I'm calculating the correlations for cols A&B and A&C. That works ok with gaps in.

I'm also calulating the statistical significances of those correlations and need to know how many rows of data have been used. I need something that can ignore the rows with missing numbers and provide a count of how many complete rows there are, in this case 3. The issue is these columns will be filtered to tailor the correlations, so the counting needs to be able to cope with that.

Does that shed anymore light?

Thanks

Add another column to the data table and put a formula in each cell that returns a 1 if all the cells in that row are filled.

Something like this...

=--(COUNT(A2:C2)=3)

Then you can use a SUBTOTAL function on that column:

=SUBTOTAL(9,D2:D6)

 

[hellcat83]

That worked great, thanks for the help guys

 

[T. Valko]

That worked great, thanks for the help guys

You're welcome. Thanks for the feedback!