# Running count with spill range, zeroing out counts >1

1. ## Running count with spill range, zeroing out counts >1

Hi,

I have attached a workbook where I perform a running count with criteria, with 1 for the first count and 0 for the next occurrences.

=IF(COUNTIFS(\$A\$2:\$A2;\$A2;\$B\$2:\$B2;\$B2)>1,0,1)

How can this be done with dynamic formulas giving a spill range? (And without Byrow and Lambda)

Best regards,
Marbleking

2. ## Re: Running count with spill range, zeroing out counts >1

Hi,

I found a solution for this:

=IFERROR(IF(MATCH(ROW(\$A\$2:\$A\$8)-ROW(\$A\$1),MATCH(\$A\$2:\$A\$8&\$B\$2:\$B\$8,\$A\$2:\$A\$8&\$B\$2:\$B\$8;0),0),1,0),0)

Regards,
Marbleking

3. ## Re: Running count with spill range, zeroing out counts >1

Thanks for letting us know.

4. ## Re: Running count with spill range, zeroing out counts >1

I'll keep this open a bit longer, please. The solution I found is slow on large data sets, so if anyone has a more efficient way, that'd be nice.

5. ## Re: Running count with spill range, zeroing out counts >1

Ok Try

Formula:
`Please Login or Register  to view this content.`

6. ## Re: Running count with spill range, zeroing out counts >1

Another SPILL-formula without LAMBDA:
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Running count with spill range, zeroing out counts >1

In D2
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)