Alternative Formula to MAXIFS

SamLev

Hi Smartsheet community!

I am trying to find the maximum value in a range with both Vertical and Horizontal criteria. I was able to get the answer I need for a similar column using SUMIFS to add all the values together. Now I need to find the max value (however, Smartsheet doesn't have the MAXIFS formula). Could someone help me turn my SUMIFS formula into a comparable MAXIFS-like formula?

Thanks so much!

=SUMIFS(INDEX({Database - API Historical Rooftops Range 1}, 0, MATCH([Partner Name]@row, {Database - API Historical Rooftops Range 2}, 0)), {Database - API Historical Rooftops Range 3}, >={BegThisYear}, {Database - API Historical Rooftops Range 3}, <={End-0})

heyjay

Consider looking up =MAX(COLLECT

SamLev

Thanks @heyjay! I tried that, but can't seem to get it to work… Would you be able to help me out?

For some more context:

{Database - API Historical Rooftops Range 2} is the partner name and is the horizontal criteria

{Database - API Historical Rooftops Range 3} is the date timestamp and is the vertical criteria. I have this in the criteria 2x as I am trying to find dates within the current year. Happy to do this a different way if that's easier.

heyjay

Let's try this:

=Max(Collect(
{Database - API Historical Rooftops Range 1},
{Database - API Historical Rooftops Range 2}, [Partner Name]@row,
{Database - API Historical Rooftops Range 3}, >={BegThisYear},
{Database - API Historical Rooftops Range 3}, <={End-0}
))

The template is for anything with Collect formula usually goes like this:

=MAX(COLLECT(
{Column-to-Calculate},
{Range1},Criteria1,
{Range2},Criteria2)

then copy line 3 as you add more criteria.

SamLev

I am getting an #INCORRECT ARGUMENT SET with that formula…

heyjay

={BegThisYear} and <={End-0}

These needs to be a cell like BeginDate@Row and EndDate@Row, these cannot be a reference. Update these with your value criteria.