1

I'm trying to write a formula that gives a running count of Issues for a given day. In other words: the output should enumerate each Issue for a given date (returning blank if Issue is blank), and then start again at 1 for the first issue in a subsequent date.

I've hard-coded the expected outputs in the "desired output" column (column I):

enter image description here

Sample dataset is in this sheet. Key pieces:

  • Column B contains the date
  • Column E contains the T-shirt size severity of each Issue
  • Column F contains a numerical translation of column E
  • Column G contains a binary output of whether there was an Issue

In my attempt (column J), I've gotten close using

=ArrayFormula(MMULT((ROW($B3:$B)>=TRANSPOSE(ROW($B3:$B))) * EXACT($B3:$B,TRANSPOSE($B3:$B))^1, ($G3:$G)^1))

...but it's not quite what I want, as:

  1. this repeats values instead of giving blanks (e.g. row 8, 11)
  2. this gives 0s instead of giving blanks (e.g. row 3, 4)

See Validation (column L).

Any ideas on how to get to what I'm looking for?

marikamitsos
  • 10,264
  • 20
  • 26
SportyJordy
  • 17
  • 3

1 Answers1

2

Just wrap your formula in the IF function

=ArrayFormula(IF(F3:F="",,YOUR.....FORMULA))

In other words

=ArrayFormula(IF(F3:F="",, 
                   MMULT((ROW($B3:$B)>=TRANSPOSE(ROW($B3:$B))) * EXACT($B3:$B,TRANSPOSE($B3:$B))^1, ($G3:$G)^1) 
                      ))
marikamitsos
  • 10,264
  • 20
  • 26