Highest Value of a Column in all Matching Rows

Ramsay Zaki

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.

http://stackoverflow.com/questions/21459534/excel-find-highest-value-of-a-column-in-all-matching-rows-with-screenshot

Shaine Greenwood

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/775363-using-formulas

Let me know if this wasn't what you were looking for and I can take another look at your process.

Ramsay Zaki

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. :-\

Shaine Greenwood

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.

Kent Vaughn

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

Kent Vaughn

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

J. Craig Williams

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

Edgar López

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

Álvaro Paniagua S

Hi everyone,

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

Tripp Boren

Thank you! That worked for my need!

James H

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