Array formulae/macro? Counting datasets -

**ad9051** · 12-19-2010, 09:44 AM

My knowledge of excel is mediocre/good, however when it comes to using array formulae I'm a little bit of an amateur. The problem I have is that I receive hundreds of datasets every day, it would be very useful to be able to count the size of these datasets (they range from 2 - 30 pieces of data in size.) Also I would like to return the size of the dataset in the same row where the data rank = 1. Note this is not always the top piece of data.

It is best to take a look at the example spreadsheet provided.

If anyone could provide any assistance it would be greatly appreciated.

Merry Xmas

Ad

**Marcol** · 12-19-2010, 10:20 AM

Try in D6

=IF(AND($B6<>"",$B7=""),COUNT($B$6:$B6)-SUM($D$5:$D5),"")

Drag/Fill Down

[EDIT]

This seems, to me at least, to better answer your problem.
Using RANK() as a condition could be a bit difficult or perhaps just misleading.
What would happen if the data-set has more than one value ranked first?

**davesexcel** · 12-19-2010, 11:55 AM

Try this

Please Login or Register  to view this content.

**Marcol** · 12-19-2010, 02:11 PM

Here's a formula approach, I perhaps missed that you also needed the ranking of each set.

In Column A (Helper Column)
In A6

Please Login or Register  to view this content.

Drag/Fill Down

In C6

Please Login or Register  to view this content.

Drag/Fill Down

In E6

Please Login or Register  to view this content.

Drag/Fill Down

I have left my original suggestion in Column D for reference only, it isn't needed with the other formulae.

This assumes that each of your data sets has a text header in Column B

Hope this helps