Progressive Count of values

[bpiereder]

Here is an example spreadsheet of what I'm trying to do. I've posted on these other forums too: OzGrid, ExcelForum


I need a way to count how many users there are with views during that or any previous week. This dataset is a typical example (though potentially hundreds of users and hundreds of weeks).


I'm not sure what formula to use here. I tried SUM with COUNT or COUNTIF, SUMPRODUCT, using array, but can't get it to work. I was able to manually create a new formula for each week that works but that's not useful for the size of the data I'm working with.


What formula should I use?

 

[pgc01]

Hi

Try in B14:

=COUNT(1/FREQUENCY(IF($B$3:B$12<>"",ROW($B$3:B$12)),ROW($B$3:B$12)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

Copy across

 

[bpiereder]

Thank you! This worked like a charm:
=COUNT(1/FREQUENCY(IF($B$3:B$12<>"",ROW($B$3:B$12)),ROW($B$3:B$12)))

Another solution from @Carim on OzGrid was:
=SUM(N(MMULT(N($B$3:B12>0),TRANSPOSE(COLUMN($B$3:B12)^0))>0))

And another with slightly different syntax from @Bo_Ry on ExcelForum worked too:
=SUM(--(MMULT(--$B3:B12,TRANSPOSE(COLUMN($B3:B12)^0))>0))