Excel Count from last specific text

H

Harry Anderson

Joined
Aug 21, 2017
Messages
2
Reaction score
1
I have a simple spreadsheet which tracks the cleaning of equipment. One column asks if they have deep cleaned the equipment or not.
The equipment only has to be deep cleaned every third clean but they might deep clean it before that.
The column has data validation applied so that the answer can only be yes or no

How do I count the amount of no's in a column from the last yes?
 
Hi Harry, I don't think that's possible without using macros, which would add to the complexity quite significantly. You could fudge it by allowing the answer to be "clean", "1st use", "2nd use", "3rd use", depending on how the equipment was left.
 
Still rather sloppy but I did manage to get what I wanted out of it:

Made a separate table to the side with an IF formula:
=IF(C4="yes",AH3*0,IF(C4="no",AH3+1,""))

Then a LOOKUP formula to get the last number on the list:
=2-(LOOKUP(2,1/($AH$4:$AH$838<>""),$AH$4:$AH$838))
 
Good thinking :). Thanks for reporting back with a solution!
 
Back
Top