formula for item no.

[Guest]

Hi is any one can help?? I need a formula can calculate the item no. ,if the
column "c" show "deleted" or column "B" show the same no. for the above row,
the formula will skip to next item. sample as follow:-

item no A B C
1 8 8-TP$3B 200E-L-75-1
- 8 8-TP$4A DELETED
- 8 8-TP$4B DELETED
- 8 8-TP$5A 200E-L-75-1
2 8 8-TP$5A 200E-L-75-1
- 8 8-TP$6A DELETED
- 8 8-TP$6B DELETED
- 8 8-TP$7A DELETED
- 8 8-TP$7B DELETED
- 8 8-TP$8A DELETED
- 8 8-TP$8B DELETED
3 8 8-TP$9 200E-L-75-1
- 8 8-TP$10A DELETED
- 8 8-TP$10B DELETED
- 8 8-TP$11A DELETED
- 8 8-TP$11B DELETED
4 8 8-TP$12A 200E-L-75-1

Many thanks
philip

 

[Daniel CHEN]

Use formlua like this
<starting from row 2>
if(or(B2=B1, C2="DELETED"),"-", sum($A$1:$A1)+1)


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
=================================

 

[Guest]

I think Daniel means COUNT at the end, not SUM.

Andy.

 

[Guest]

thanks fro advise however,
I try both formula, it doesn't work
start at row A12
=IF(OR(C12=C11,AI12="Deleted"),"-",SUM($A$6:$A12)+1)
or
=IF(OR(C12=C11,AI12="Deleted"),"-",COUNT($A$6:$A12)+1)

am I missing something ...??

please advise