 SOLVED

New Contributor

# Progressive numbering with variables

Hello everybody. Hope you can help me.

I'm looking for a compound formula that allows me to result in an incremental number based on two variables.
Column A -> I have a series of dates they can be also duplicated
Column B -> I have values 0 or 1
If the cell in column B equals 0 the result will always be -1, this is simple.
Therefore:

Cell C2 = -1 because B2 = 0

Cell C1 = 1 because cell B1 = 1 and in the set of column A where the dates are May 25 this is the first row of this range

Now comes the problem that I can't solve:
Cell C3 = 2 because B3 = 1 and A3 is part of the "May 25"'s range and is the second occurrence in which this two rules are verified.

And so on.

 A B C 1 25-mag 1 1 2 25-mag 0 -1 3 25-mag 1 2 4 25-mag 1 3 5 25-mag 0 -1 6 25-mag 1 4 7 26-mag 1 1 8 26-mag 1 2 9 26-mag 0 -1 10 26-mag 1 3

Can someone help me?

Michele.

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Progressive numbering with variables

``=IF(B1=0,-1,COUNTIFS(\$A\$1:A1,A1,\$B\$1:B1,1))``

You can try this formula. # Re: Progressive numbering with variables

@Michele711855 As a variant:

``=IF(B1=0,-1,SUMIF(\$A\$1:A1,A1,\$B\$1:B1))``

# Re: Progressive numbering with variables

Ohhh, so simple...
Thanks.

Thanks Riny