Array formula help, counting size of a dataset!

**ad9051** · 12-09-2010, 09:17 AM

Hello, i am relatively new to the use of array formulae, but am starting to become aware of their huge power and abilities over regular formulae.

I produce and recieve spreadsheets which can have hundereds of datasets in ranging from 1- 30 pieces of data, Each dataset is seperated spatialy, and as such i beilieve an array formula could be used to return the size of the datasets. I need an array formulae which can essentially count the number of cells in each dataset, and return the value where the rank = 1.

Please see example.

Many thanks for any contributions.

Ad

**NBVC** · 12-09-2010, 09:53 AM

Try, in F5:

=IF(D5=1,COUNT(D5:INDEX(D5:D$10000,MATCH(TRUE,INDEX(D5:D$10000="",0),0))),"") copied down

where D10000 is the last row that data may be contained in...

Note this is an array formula, although you do not need the CTRL+SHIFT+ENTER confirmation, so try not to go too large on the ranges used.

**NBVC** · 12-09-2010, 09:57 AM

A little more efficient way, would be to add a helper say in E5 with formula:

=IF(AND(D4<>"",D5=""),1,"")

copied down as far as needed,

Then in F5:

=IF(D5=1,COUNT(D5:INDEX(D5:D$10000,MATCH(1,E5:E$10000,0))),"")

copied down as far as needed, again adjusting the 10000 upper bound as required.