Per-cell formula for tabulating daily running counts of a given column

0
votes

Follow-up to question 63893967:

How can I achieve the same result of...

giv[ing] 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

...but not use an ArrayFormula? i.e. I'd like to have a formula in each row, but the same values outputted as column J. The reason is that the iOS app crashes every time I open this spreadsheet on it now that I have the ArrayFormula in there.

Sample spreadsheet, looking on "Formula per row method" tab:

Screenshot

  • 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 that row
  • Column I contains the ArrayFormula method:

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

  • Column L reconciles the attempt in column J against the source of truth in column I
1
google-sheetsgoogle-sheets-formulaarray-formulas
Can you try just to remove the arrayformula: =IF(F13:F="",, MMULT((ROW($B13:$B)>=TRANSPOSE(ROW($B13:$B))) * EXACT($B13:$B,TRANSPOSE($B13:$B))^1, ($G13:$G)^1)) – soMario
Sadly, this doesn't work :( – SportyJordy

1 Answers

2
votes

The simplest thing to do would just be this:

=IF(F3="",,
    INDEX(
        MMULT(
            (ROW(B$3:B)>=TRANSPOSE(ROW(B$3:B))) * 
                EXACT(B$3:$B,TRANSPOSE(B$3:B)),
            G$3:G
        ),
        ROW(F3)-2
    )
)

...if you're ok with MMULTing at every row. I can see this getting slow for large data sets, though, assuming Sheets does in fact calculate this per every row. Of course, you can be more efficient if you memoize the MMULT result in another sheet and use that range.

Memoized MMULT()

We can create a new sheet (I called it "Memo") to memoize, or cache, the MMULT() so it only has to be calculated once.

=ArrayFormula(MMULT(
    (ROW('Formula per row method'!B3:B)>=TRANSPOSE(ROW('Formula per row method'!B3:B))) * 
        EXACT('Formula per row method'!B3:$B,TRANSPOSE('Formula per row method'!B3:B)),
    'Formula per row method'!G3:G
))

Then your original formula becomes:

=IF(F3="",,
    INDEX(
        Memo!A:A,
        ROW(F3)-2
    )
)

However, I'm not sure if this will work for you because you said ArrayFormula() wasn't working. It might just be breaking in your original use case, though, so it's worth a shot.