Running count of occurrence in list using array formula with spill range

  • Hi,


    I am looking for an array formula that can perform a running count of occurrences for items in a list and spill the results in a spill range. Until now I have been using the following Countif formula:


    Code
    1. =COUNTIF(A$1:A1;A1)


    Apples1
    Apples2
    Bananas1
    Peaches1
    Grapes1
    Apples3
    Bananas2
    Grapes2
    Oranges1
    Oranges2
    Grapes3
    Oranges3


    Best regards,

    Range Rover

  • Hi royUK!


    I am using Microsoft Excel for Office 365 MSO (16.0.12527.20612) 32-bit. The formula I am looking for will be used as part of a workbook I am constructing, so Pivot Table is not feasible this time. I could of course keep on using the =COUNTIF(A$1:A1,A1) formula, but is looking for something that doesn't require copying the formula down for as many rows as there are data that need counting. The data that needs counting constitutes a dynamic range and I would like the counting formula to also be dynamic, i.e. an array formula with a dynamic spill range.


    Regards,

    Range Rover

  • Hi,


    Nice solution nevertheless, XOR LX!


    This is from another benefactor:


    =MMULT((A1:A12=TRANSPOSE(A1:A12))*(SEQUENCE(ROWS(A1:A12))>=SEQUENCE(,ROWS(A1:A12))),SEQUENCE(ROWS(A1:A12))^0)


    Regards