Counting populated rows

Hi all,

I'm looking for a formula and wondering if anybody can assist. There are the rules I need to follow.

1. There are two columns with multiple unknown rows.
2. Column 1 on any given row may or may not be populated with a value.
3. In column two, the first cell in column 1 that is populated must equal 10.
4. The second cell that is populated must equal 9, and so on down to 1.

The purpose is to show that I have 10 slots available in a given box and to advise when the box no long has slots available.

Can anybody assist with a formula that might be helpful in this case.

Cheers

Result.xlsx

Originally Posted by martindwilson

something like
=IF(A2="","",11-COUNTA($A$1:A2)) filled down maybe
or just i one cell put =MIN(MAX(0,10-COUNTA(A2:A5000)),10) which will count down to 0

Thanks for your response Martin

I've tried the filled down method, which works one the first run. Or rather, it works when directly entering data into the cell, but I'm actually using a data validation list to change the cell value. I presume this has something to do with a NULL value.

That's when the formula starts to behave oddly. i.e it counts down all cells, rather than just those with values.

I've created this in a worksheet. please see attached.Attachment 277669


Cheers

This is brilliant and much appreciated. Thanks Martin.

It occurs to me that I didn't think about one final element of this formula.

I wonder if it's possible to restart from 10 whenever the counter reaches 0?

My apologies. It was not clear.

The formula you provided, =IF(B3="","",10-SUMPRODUCT(--($B$3:B3<>""))), works perfectly to countdown from 10 the number of occurances that text appears in the preceeding cell.

The result is 10, 9, 8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5,-6,-7,-8,9,-10,...etc

Unfortunately I have thousands such rows. My objective is to identify when a box has been filled and then when I can start a new box. I therefore need the following result:

10, 9, 8,7,6,5,4,3,2,1,10, 9, 8,7,6,5,4,3,2,1...etc

I have tried using the Mod function, and also tried to using another cell to identify where the 1 is so that I can start again, but it generally fails becuase of the specifics of the sum product, being that it requires a fixed start location. It appears that it would require a nested solution, but Im afraid i'm just not cluey enough to think through functions to that depth.

Does this make better sense? I hope you can help.

Thanks for your efforts so far.

Cheers

Okay. I kinda figured it was beyond the capabilities of an excel formula. it may require a VBA solution (which I didn't want to do).

Thanks again.

I'm a bit of a dummy. I should have known better. The end result was:

,MOD(COUNTIFS($B$6:$B6,"Test 1") and filled down. This works exactly as intended.

Thank you so much for your help.