Count how many times a cell value changes across a row

megan.griffiths

Hi all,

I'm stuck on a formula - I am trying to find a way to count how many times a cell value changes across a row e.g.:

How many times does the adviser name change across all these rows, and calculate in the end column called"Count"?

Would be grateful for any help.

Thanks,

Meg

Andrée Starå

Hi @megan.griffiths

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Another option could be to have a workflow copy the row to another sheet each time it changes,a nd then use cross-sheet formulas to count how many times it happened.

Make sense?

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

megan.griffiths

Hi @Andrée Starå - thanks for the response but i'm not sure I'm following. This is all static data and won't change again, I just simply want to count how many times an adviser has changed over the last 6 months, so I'm trying to figure out a formula to help me do this as there are over 5000 clients on the list.

Any help would be appriciated.

Thanks,

Meg

Andrée Starå

@megan.griffiths

You're more than welcome!

Try something like this.

=COUNT(DISTINCT([Client Adviser 11.01.21]@row:[Client Adviser 12.04.21]@row))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

megan.griffiths

Hi @Andrée Starå - thanks so much, I think that formula has worked. You're a star! ⭐️

Meg

Andrée Starå

@megan.griffiths

Excellent!

Happy to help!

Thanks for the kind words!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!