How remove a character ">" in order to SUM values in one column if they meet a criteria.

Samantha Franco

I need to be able to get the results using a formula. (I can't use a helper column, or the find and replace to remove ">".

Logic needed:

This formula should substitute the greater-than sign with nothing and convert those to numbers. Then it will need to sum the numbers where the criteria is met (in this case the condition will be the team). Then it need to divide that sum by a count for each team.

Mark Cronk

Hi Samantha,

No helper makes this tough. I don't know if this will work. Cross your fingers and try:

=SUM(SUBSTITUTE(JOIN(COLLECT(aging:aging, team:team, team@row), ","), ">",""))

The row the formula is in will need a Team column. Instead of Team@row you could input the value, e.g. "Team A".

Work or error?

Mark

Samantha Franco

https://community.smartsheet.com/discussion/comment/294444#Comment_294444

Hi Mark, thanks for your help. Unfortunately this did not work. The result I get back is 0 no matter what team@row is used.

Mark Cronk

Hi Samantha,

Try the formula pieces to figure out where the problem is:

=JOIN(COLLECT(aging:aging, team:team, team@row), ",")

=SUBSTITUTE(JOIN(COLLECT(aging:aging, team:team, team@row), ","), ">","")

Do they result in strings of numbers?

Mark