 # 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)

 Apples 1 Apples 2 Bananas 1 Peaches 1 Grapes 1 Apples 3 Bananas 2 Grapes 2 Oranges 1 Oranges 2 Grapes 3 Oranges 3

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,

=COUNTIF(OFFSET(A1,,,SEQUENCE(ROWS(A1:A12))),A1:A12)

though it would be nice to find a non-volatile set-up.

Regards

• 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