How remove a character ">" in order to SUM values in one column if they meet a criteria.
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.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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.
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!
