Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Highest Value of a Column in all Matching Rows
I am trying to find the highest value of column B among all matching values in column A. I know how to do it in Excel (see link below) but I cannot figure out how to tweak the formula to work in Smartsheet.
Comments

Hello Ramsay,
The MATCH function takes an optional argument that will find the highest value for you, the syntax looks like the following example:
=MATCH("Task C", [Task Name]1:Done5, 1)
The last 1 after the comma is an optional argument that tells the MATCH function to look for the highest value. Details on the function can be found on our formulas page: https://help.smartsheet.com/articles/775363usingformulas
Let me know if this wasn't what you were looking for and I can take another look at your process.

I don't see how match does what I need. Consider the following dataset:
Row # Task Name Value
1 A 2
2 B 6
3 A 5
4 E 8
5 F 1
6 A 3
I would like to find the highest value for Task Name 'A'. The result should return '5'.
Using MATCH against the above dataset, it returns '2' every time (which is the index # of the first time it finds value 'A'). I also tried using some sort of combination of the MATCH and INDEX functions but couldn't figure it out. :\

Hello Ramsay,
Sorry for misunderstanding and thanks for the clarification. It appears that Smartsheet isn't capable of implementing this type of formula, the crux being the MAX(IF() portion of that VB formula in the stack overflow example—which Smartsheet can't do. We also don't seem to have a comparable workaround with our formulas that can achieve what you're looking for.
Sorry again if I got your hopes up! I'm going to pass this feedback along to our Product team for further review.

Ramsay  Was there ever a solution to this problem? Seems like a somewhat common use case to find a MAX value, but only on conditional rows. I have the same use case and would love to know if you solved this.
 Kent

I did end up finding a "workaround" for this issue. What I did was to create a new column ( far left of my data, hidden ) where I did my conditional logic and set a flag if the row met the conditions. Then I pulled over the data into that column. Then I used the MAX() function on that column. It's not elegant, but it works.
SS should continue to expand their function set to include advanced functions for all condtional logic, similar to SUMIFS, COUNTIFS, etc.
As an example from above:
Create New Column, FlagField
Fill FlagField with a formula =IF([Task Name]1="A", [Value], "" )
Then you can use your the max function: =MAX( [FlagField]:[FlagField])
Row # Task Name Value FlagField
1 A 2 2
2 B 6
3 A 5 5
4 E 8
5 F 1
6 A 3 3

Kent,
That's what I came up with as well.
The only change I had was to to set the criteria to a cell:
=IF([Task Name]1 = $MyMax$2, Value1)
so I could build several of these if I wanted to either:
1. change the choice on the fly
or
2. capture them all in the header (one column per choice, unfortunately)
Craig

HELLO;
I HOPE YOU CAN HELP ME WITH THIS PROBLEM:
I NEED TO SCRATCH A FORMULA TO GENERATE DEPENDENCIES AUTOMATICALLY IN A PROJECT SHEET WITH THOUSANDS OF ROWS.
EACH ROW CORRESPONDS TO A TASK REPEATED RANDOMLY. AND MY PROJECT HAS MORE THAN 70 DIFFERENT TASKS, SO IT IS VERY LABORIOUS TO ASSIGN DEPENDENCIES USING FILTERS BY TASK.
ATTACHED THE LINK OF MY SHEET EXAMPLE IN WHICH I HAVE TRIED TO SOLUTION IT USING THE FUNCTIONS:
LOOKUP + MATCH INDEX + MATCH
THANKS IN ADVANCE
https://app.smartsheet.com/b/publish?EQBCT=269e4f8a72d74b83a84357d386756959
<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="http://publish.smartsheet.com/fb9df41f04754198b5e93de66eef8b54"></IFRAME>

Hi everyone,
I found this solution: MAX(COLLECT()). Does it work for you?

Thank you! That worked for my need!

Thanks Alvaro, MAX(COLLECT()) worked for me.