Excel Count from last specific text


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?
 
Ad

Advertisements

Ian

Administrator
Joined
Feb 23, 2002
Messages
19,150
Reaction score
1,083
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.
 
Joined
Aug 21, 2017
Messages
2
Reaction score
1
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))
 
  • Like
Reactions: Ian
Ad

Advertisements

Ian

Administrator
Joined
Feb 23, 2002
Messages
19,150
Reaction score
1,083
Good thinking :). Thanks for reporting back with a solution!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top