Forum Discussion
Excel formula
Hi Please can someone in the community help. I have some data in a formatted table and I am looking for a suitable formula that will help me with the following: Column C contains a list of ...
Let's say the data start in row 2.
In C2:
=IF(OR(J2={"Completed","Let","Sold"}), SUM(COUNTIF(J$2:J2, {"Completed","Let","Sold"}))+4732, "Pending")
Fill down
- Thank you so much Hans it works really well. One final question, how do I amened your suggested statement so that if Column J is set to Credit Note, the input in C2 would be Credit Note 008. The next entry below would need to continue the sequence of invoice numbers.
Many thanks
PhilLike this:
=IF(OR(J2={"Completed","Let","Sold"}), SUM(COUNTIF(J$2:J2, {"Completed","Let","Sold"}))+4732, IF(J2="Credit Note", "Credit Note 008", "Pending"))
Hi Hans, You have been so very helpful.
My formula now reads:
=IF(OR(A2={"COMPLETED"}), SUM(COUNTIF(A$2:A2,{"COMPLETED"}))+4732, IF(A2="CREDIT NOTE", "CREDIT NOTE 008", "PENDING"))
How do I make a final adjustment so that when there is another credit note in the data set it comes up with the next number in the sequence i.e. credit note 009 and so on and so.
I really appreciate your input / assistance.
Regards
Phil
