Count until a condition is met

**SueWithQuestion** · 02-21-2011, 12:45 PM

Attached is a sample worksheet.

The Status column contains a small number of possible values. I want to only count records whose Status value is either "Yes" or "No", and I only want to count until the sum of the "Yes" and "No" records equals the number specified in cell F2.

In other words, in this sample worksheet, the Number Yes result should be 4, and the Number No result should be 2 (and then the counting would stop because 6 records have been counted).

What is the best formula to do this?

**MarvinP** · 02-21-2011, 12:58 PM

Hi Sue,
Try the attached.

**SueWithQuestion** · 02-21-2011, 01:08 PM

Thank you so much for your quick reply. Your formula is close, but not quite what I'm looking for.

The result for the "Number Yes" formula should be 4, not 6, because I only want to keep counting until the sum of "Number Yes" and "Number No" equals the "Goal" value of 6.

Any suggestions?

**MarvinP** · 02-21-2011, 01:29 PM

Here it is, now that I understand the question.
I needed a helping column and a indirect range cell, but this will do it.

You might be able to combine them into a single equation but I'm not that smart (or motivated

)

**SueWithQuestion** · 02-21-2011, 01:40 PM

Thank you again!

I would like to figure out a way to not have to add a helper column to do this.

Can anyone else offer another suggestion?

**DonkeyOte** · 02-22-2011, 03:29 AM

I'd still avoid repeating expensive calculations but you could use a single helper cell rather than a column full

Please Login or Register  to view this content.

the above Array will determine the last "valid" row - ie the point at which the Goal is reached

Please Login or Register  to view this content.

Though you could avoid use of G2 it makes little sense to do so.
The end point is constant for both Yes & No results thus there is little point recalculating that value more than once.