Search code examples
excelexcel-formulaformulaexcel-tables

Excel: Running occurrence of items in table column

I am trying to achieve this in a table in Excel: https://exceljet.net/formula/running-count-of-occurrence-in-list

So, counting each item in the list in an ordered sequence:

enter image description here

(I want it for Red and Green too, of course)

If I however convert it to a table, and add something like these variants, then I get a total instead for each. Is there a way to achieve this in a table column? The only other answer I found to this was within Power Query, which I won't be using for this table:

=IF([@Color]="blue",COUNTIF([Color]:[@Color],"blue"),"")
=IF([@Color]=[@Color],COUNTIF([Color]:[@Color],[@Color]),"")
=IF([@Color]="blue",COUNTIF([@Color],"blue"),"")
=IF([@Color]=[@Color],COUNTIF([@Color],[@Color]),"")
Solution

  • You can use INDEX() to refer to the range at hand per row:

    enter image description here

    Formula in B2:

    =SUM(--(INDEX([Color],1):[@Color]=[@Color]))

    Meaning:

    • INDEX([Color],1) - Always refer to the first row in the "Color" column;
    • : - Continue creating a valid range reference;
    • [@Color] - Refer to the current row/value.
    • =[@Color] - Match the above structure against the current row/value.
    • -- - Turn the TRUE/FALSE array into 1/0 values.
    • SUM() - Sum the given array to a total.

    Note, the website you have tagged is quite handy, they also have exactly what you are after covered on another page. See this.

    Also, I guess this needs to be CSE-entered in pre-365 versions of Excel.