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
-
If you do not already have an Auto# column then add one called LINE-ID.
The create a checkbox column and add the below formula. This will make the checkbox checked if it is a new client.
=IF(MATCH(client@row, client:client, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0),1,0)
Make sure to replace the column names above to match what you have on your sheet...
You can then run count functions on this column based on 1 (new client) and 0 (existing client).
-
By running a match on the LINE ID i get the current row #.
We also run a match on the client. If the match returns the same # that means that the first time this client shows up on the sheet is on the current row.
If they do not match that means the first time it shows up is on a previous row and they are an existing client...
-
@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
Answers
-
If you do not already have an Auto# column then add one called LINE-ID.
The create a checkbox column and add the below formula. This will make the checkbox checked if it is a new client.
=IF(MATCH(client@row, client:client, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0),1,0)
Make sure to replace the column names above to match what you have on your sheet...
You can then run count functions on this column based on 1 (new client) and 0 (existing client).
-
@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.
-
By running a match on the LINE ID i get the current row #.
We also run a match on the client. If the match returns the same # that means that the first time this client shows up on the sheet is on the current row.
If they do not match that means the first time it shows up is on a previous row and they are an existing client...
-
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
Categories
Check out the Formula Handbook template!
