0

I would like to keep a running total in column A if certain text is present in column B. If that text isn't present, I would like column A to be blank, but pick up counting where it left off on the next time the criteria is met.

Example: When column B contains blue, yellow, or red, count in column A, lest leave blank

A B
1 blue
2 yellow
green
orange
3 blue
green
4 red
0

Assuming the color names are in column B2:B, use countifs() like this:

=arrayformula( 
  if( 
    regexmatch(B2:B, "^(blue|yellow|red)$"), 
    countifs( 
      row(B2:B), "<=" & row(B2:B), 
      regexextract(B2:B & "µ", "(?:blue|yellow|red)(µ)"), "µ" 
    ), 
    iferror(1/0) 
  ) 
)
5
  • I tried that and updated it to fit my exact search and I received an Error "Array result was not expanded because it would overwrite data in A2"
    – Denny
    Aug 18 at 16:43
  • The formula in the answer is an array formula that fills the whole column automatically. Clear column A before you insert the formula to make room for the results. Aug 18 at 17:22
  • I'm realizing now that I completely messed up my question looking back at my sheet. Here is my document if you're willing to assist further. I'm a baseball coach creating a pitch tracking document. So after a result, I want to start a new batter, hence the counting. However, the running total would be on the next row down. docs.google.com/spreadsheets/d/…
    – Denny
    Aug 18 at 17:37
  • OK, that is a different question. Please post it separately. Show your hand-entered expected results in the sample spreadsheet. Aug 18 at 18:05
  • I'll do that right now. Thank you.
    – Denny
    Aug 18 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.