New client vs. continued client


I have a sheet where I list all advice give to clients (each row is a separate entry for advice given to a particular client). I want to add a column that will tell me if the client is a new client or if the client has received advice before (calculating based on first instance client appeared on sheet). And then on a different sheet, I want to be able to, by month, count how many clients were new clients vs. continued clients, in that month.

I'm not sure how to set this up (or if it possible). I think adding a column on the main sheet and using a formula to identify if they are a new client or continued is step one. But I'm not sure. I've tried a couple different options and haven't got anywhere.

Once I have the above figured out, I'm pretty sure I can use a basic COUNTIFS formula based on month/year to count the new vs. continued clients.

Anyone have insight on how I can do this?

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Jennifer Lenander What matters is that you have something working, but here is a quick rundown of my formula...

    We start with the COUNTIFS. Using Client$1 means the range starts in row 1. Then we use the @row reference on the other side of the range to count down to the current row. It basically says to count how many times from row 1 down to the current row does that client name exist.

    Then we use the IF statement to say that if that count is 1 (meaning it is the first time that particular text is found from the top of the sheet down to the current row), we output a 1, otherwise 0. So you will have a 1 in the first occurrence and a 0 in all of the others below it.

    The difference with mine is that using the absolute reference to lock in row 1 for the start of the range means that it cannot be used as a column formula which means dragfilling, but it allows for a single column instead of two columns.

    It is simply a matter of personal preference. I have done so much work in Smartsheet before column formulas were a thing, so my thought/solution process hasn't quite pulled that in yet. Haha


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!