Forum Discussion

Copper Contributor

Feb 06, 2024

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 ...

excel

HansVogelaar

MVP

Feb 06, 2024

Phil_887

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

Phil_887

Copper Contributor

Feb 06, 2024

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

Phil

HansVogelaar
MVP
Feb 06, 2024
Phil_887

Like this:

=IF(OR(J2={"Completed","Let","Sold"}), SUM(COUNTIF(J$2:J2, {"Completed","Let","Sold"}))+4732, IF(J2="Credit Note", "Credit Note 008", "Pending"))
- Phil_887
  Copper Contributor
  Feb 07, 2024
  HansVogelaar
  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
  - HansVogelaar
    MVP
    Feb 07, 2024
    Phil_887
    
    Since you now only look at COMPLETED, part of the formula can be simplified.
    
    =IF(A2="COMPLETED", COUNTIF(A$2:A2,"COMPLETED")+4732, IF(A2="CREDIT NOTE", "CREDIT NOTE "&TEXT(COUNTIF(A$2:A2, "CREDIT NOTE")+7, "000"), "PENDING"))