# 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?

• ✭✭✭✭✭✭

@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

• ✭✭✭✭✭

@Leibel S - this worked! This is awesome. Can I ask, how it works? Why is the LINE ID/Auto Number needed? Thank you so much for your help. I have been trying to figure this out for longer than I would like to admit.

• ✭✭✭✭✭

I get it! Thank you. That is pretty awesome. You are in a close race with @Paul Newcome for being my hero. :)

• ✭✭✭✭✭✭

@Jennifer Lenander Haha. As long as you are getting the answers you need.

If you are not using those new fangled column formulas (@Leibel S 😜), you can actually do this in a single column using a very similar logic.

=IF(COUNTIFS(Client\$1:Client@row, Client@row) = 1, 1, 0)

I guess I should quit being behind the times and get the hang of those things at some point. Haha

• ✭✭✭✭✭✭

On most my sheets I end us using the LINE ID / ROW# for so many things that the work becomes worthwhile...

• ✭✭✭✭✭

@Leibel S and @Paul Newcome - thank you both. I greatly appreciate both of you.

FWIW, I am able to follow Leibel's formula a little better than Paul's. Thanks again! :)

• ✭✭✭✭✭✭

@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

• ✭✭✭✭✭✭

@Leibel S I put the row number column on every single one of my sheets right off just in case I end up needing it. I have done it without the column references so many times, that I don't even think about it. Haha

• ✭✭✭✭✭

@Paul Newcome - this is so incredibly helpful. I really appreciate the education. I can copy/paste formulas all day but I really appreciate learning how they work so I can hopefully create them myself. Thank you for taking the time to explain, I am very grateful.

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!