conditional runing number in Excel 2007 1

[johnhugh]

Hi,

I have a spreadsheet like below and require the numbering to be like shown in the 3rd column.
Basically if the numbers in the first column and the string in the 2nd coloumn are identical to the previous row it starts counting. Other wise just give me a 1.

Thats the formula I come up with but the counting is not right.
=IF(AND($A1=$A2,$C1=$C2),COUNTA(A1:$A1)

101010100002 20-SDV01-USD 1
101010100003 20-SDV01-USD 1
101010100007 20-BML01-ZMK 1
101010100007 20-BML01-ZMK 2
101010100007 20-BML01-ZMK 3
101010100007 20-BML01-ZMK 4
101010100007 20-BML01-ZMK 5
101010100007 20-DEQ01-ZAR 1
101010100007 10-SFI02-AUD 1
101010100026 24-SFI01-USD 1

Any help much appreciated.

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT(($A$1:A1=A1)*($C$1:C1=C1))
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you had a Heading row, and EVERY table SHOULD have a heading row and NEVER start with data in A1, then this formula would also work...
[tt]
D2: =IF(AND(A2=A1,C2=C1),D1+1,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

and since you're using Excel 2007, you could also use the COUNTIFS function...
[tt]
=COUNTIFS($A$1:A1,A1,$C$1:C1,C1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnhugh]

Awesome. Thank you. All 3 work!