+ Reply to Thread
Results 1 to 4 of 4

Progressive Count of values

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Progressive Count of values

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

    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?

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Progressive Count of values

    Please try at B14 Press Ctrl+Shift+Enter and copy to the right

    =SUM(--(MMULT(--$B3:B12,TRANSPOSE(COLUMN($B3:B12)^0))>0))

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Progressive Count of values

    =sum(n(countif(offset($b3:b3,row(1:10)-1,),">0")>0)) cse

  4. #4
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Thumbs up Re: Progressive Count of values

    Thank you! This worked like a charm:
    =SUM(--(MMULT(--$B3:B12,TRANSPOSE(COLUMN($B3:B12)^0))>0))

    Another variation worked with slightly different syntax from @Carim on OzGrid was:
    =SUM(N(MMULT(N($B$3:B12>0),TRANSPOSE(COLUMN($B$3:B12)^0))>0))

    And another working solution from @pgc01 on MrExcel was:
    =COUNT(1/FREQUENCY(IF($B$3:B$12<>"",ROW($B$3:B$12)),ROW($B$3:B$12)))

    I couldn't get the suggestion from @tim201110 to work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Progressive subtracting based on other cell values
    By The Fly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-04-2015, 06:50 PM
  2. Replies: 2
    Last Post: 01-21-2014, 02:53 PM
  3. [SOLVED] Check if the values of a list are in progressive order.
    By Askalian in forum Excel General
    Replies: 11
    Last Post: 01-24-2013, 08:56 PM
  4. [SOLVED] How to find out progressive random/harmonized numbers between to defined values
    By snehpwan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2012, 10:01 AM
  5. Replies: 0
    Last Post: 06-14-2012, 09:59 PM
  6. Progressive Count
    By score in forum Excel General
    Replies: 27
    Last Post: 06-09-2011, 10:48 PM
  7. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1