CODEWITHSUNDEEP
excelcount
Binx
Binx

Reputation: 414

Counting a specific amount of yes/no based on changing value

I'm sorry if this is a really simple questions, but I have not been able to find a solution. I want/think I need to use the count function. I have a column that contains YES and NO. I want to set up an equation that will count the number of YES based on another number. Then count the number of NO until reaching the #YES. For example

    YES/NO       Count #YES       NO Result
      YES            3                5
      YES            5                6
      NO             7                7
      NO
      NO
      NO
      NO
      YES
      NO
      YES
      YES
      YES
      NO
      YES

Column YES/NO is given Column Count #YES is given Column NO Result is found

For the first go around, the function would count 3 YES in the YES/NO column. Then count how many NO are in between the first YES and the 3rd YES. There are 5 NO in this example. For the second go around, the function would count 5 YES. Then count how many NO are in between the first YES and 5th YES. There are 6 NO in this example.

There wont actually be a Count #YES Column. Instead this value will be stored in say A1. This value will change (based on other criteria in my spreadsheet).

Cheers!

Upvotes: 0

Views: 472

Answers (2)

Mikku
Mikku

Reputation: 6664

I have applied the function in the attached workbook:

Workbook

Used the below functions:

enter image description here

Then Match to get the Result:

enter image description here

Result:

enter image description here

Upvotes: 1

BigBen
BigBen

Reputation: 50007

I think something like this should do it. You can wrap this in IFERROR to handle the possibility of there not being enough instances of "YES" in the column, for example 8 in your sample data.

=AGGREGATE(15,6,ROW($A$1:$A$14)/($A$2:$A$15="YES"),B2)-B2

enter image description here

Upvotes: 1

Related Questions