yogi_Compute Row By Row Running Count Of Combination Of Entries In Columns A and B

Google Spreadsheet   Post  #2272

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-01-2017

question by: DesignerRachel

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/CHX6xdJnXm4/Zogr2r9UCQAJ
Can you use CountIf to count instances of a pair up to the current row?
I found a formula that is on the right track to answer my question here:

=ArrayFormula(COUNTIF(if(E2:N="X", row(E2:N)),row(E2:N)))

My struggle at this point is instead of specifying the match in the formula itself, how can I say "the match from this line"?

For example:

Color       Fruit

Red         Apple

Blue        Berry

Red         Apple

Yellow      Pineapple

Red         Strawberry

Red         Apple

Red         Strawberry

Black       Berry

Red         Apple

Yellow      Pineapple

Black       Berry

Red         Strawberry

Red         Apple

Red         Strawberry

I'd like to determine how many combinations are there up to the current row and have that appear in each row.  I already have a column that just combines "Color" and "Fruit" into "ColorFruit" that I can use, I just don't know how to write the array formula. I'm not even sure it's possible with an array formula.

This is what it would look like though:

Color       Fruit             Instances so far

Red         Apple             1

Blue        Berry             1

Red         Apple             2

Yellow      Pineapple         1

Red         Strawberry        1

Red         Apple             3

Red         Strawberry        2

Black       Berry             1

Red         Apple             4

Yellow      Pineapple         2

Black       Berry             2

Red         Strawberry        3

Red         Apple             5

Red         Strawberry        4