What is an excel formula that will return me the last row number of a cell within a range that contains specific data?

Solution 1:

Assuming the data is contiguous (i.e. "Yes, Yes, Yes, No, No" but not "Yes, Yes, No, Yes, No") then you can use the following formula:

=INDIRECT("B" & (COUNTIF(A2:A6, "Yes")+1))

This is the data I used:

   | A      B
---------------
 1 | Yes    1
 2 | Yes    1
 3 | Yes    2
 4 | No     2
 5 | No     3

There are other ways to do it if you can add a sequence column, then use COUNTIF and VLOOKUP to do the same thing.

Solution 2:

Given an example of Column E containing the following:

  Row#  Value

     1. Yes
     2. No
     3. Yes
     4. Yes
     5. Yes
     6. Yes
     7. No
     8. No
     9. Yes
     10. No

You can use what's called an Array formula to accomplish this very easily. In this example, you would type the formula =MAX(ROW(E1:E10)*IF(E1:E10="Yes",1,0)) and press CTRL+SHIFT+ENTER to input the formula as an array formula Excel will add {} around the outside of the formula to indicate that it is an array formula.

Solution 3:

I'd use INDEX rather than INDIRECT as it is non-volatile, but the concept would be almost identical if all the Yes answers are contiguous and not split by No's.

=INDEX(B1:B100,COUNTIF(A1:A100,"Yes"))

With 100 replaced by however many rows you really have