Search code examples
formulalibreofficelibreoffice-calc

LibreOffice: how to increment numbers based on conditions

I am struggling to write a formula in order to increment numbers only on certain conditions.

Lets say I have three columns (A,B,C) (Picture attached). Where Column A and B are pre-populated and Column C needs to be filled with a formula.

Such that, the number in column C should increment only if column B value changes, and it should be reset to 1 if column A value changes.

What I tried:

=IF(AND(A2=A1,COUNTIF(B:B,B2)=1),C1+1,1)

also:

C = 1
=IF(A1<>A2;1;IF(AND(A2=A1;B2=B1);C1;C1+1))

but failed to achieve the desired results, thank you for all the help. cheers

column A    column B    column C
10          4           1
10          4           1
10          3           2
10          3           2
20          5           1
20          4           2
20          4           2
30          9           1
30          7           2
40          6           1
40          5           2
40          5           2
40          3           3
40          2           4

EDIT:

I figured out the correct formula:

=IF(A3<>A2,1,IF(B3=B2,C2,C2+1))
Solution

  • The second formula can be shortened.

    =IF(A1<>A2;1;IF(B2=B1;C1;C1+1))

    However, your version also produces the desired results. The question is, why did it not work for you? Perhaps you failed to drag the formula down to fill properly.

    By the way, the formula with COUNTIF(B:B) does not work because it is true if the value is anywhere in the column, not only the next one.