SOLVED

Progressive numbering with variables

Copper Contributor

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.

 ABC
125-mag11
225-mag0-1
325-mag12
425-mag13
525-mag0-1
625-mag14
726-mag11
826-mag12
926-mag0-1
1026-mag13

 

Can someone help me?


Thank you for your help,
Michele.

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

@Michele711855 

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

You can try this formula.

if countifs.JPG 

@Michele711855 As a variant:

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

 

Ohhh, so simple...
Thanks.
Thanks Riny ;)
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Michele711855 

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

You can try this formula.

if countifs.JPG 

View solution in original post