1

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]),"")
JvdV
  • 70,606
  • 8
  • 39
  • 70

1 Answers1

0

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Great! I was looking for a way to formulate mixed references for table columns, but could not find it at all. Only regular ranges like $A$1:A1 for example. This does it. Thanks! – Streching my competence Mar 24 '21 at 15:10