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 ...
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
Many thanks
Phil
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"))
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
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"))
- Hi Hans,
Thank you so much for your replies. I am the MD of a small Commercial Real Estate advisory company here in the UK. I have a reporting function to the other board directors / owners and would like to improve my use of Excel so that I can increase my efficiency/ productivity and ability to report accurate financial data.
Are you able to provide weekly one to one tuition via a Teams call so that I can share my data set with you and improve my use of Excel.
My company is Brecker Grossmith. E-mail: email address removed for privacy reasons / UK cell: +44 (0) 7842 385 898. Please can you let me know your thoughts and confirm if you can help with this. How much you would charge for your time?
I look forward to hearing from you.
Regards and best wishes
Phil Richards MRICS
