# Alternative Formula to MAXIFS

Options

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})

Tags:

• ✭✭✭✭✭
Options

Consider looking up =MAX(COLLECT

...

• Options

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.

• ✭✭✭✭✭
edited 07/30/24
Options

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.

...

• Options

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

• ✭✭✭✭✭
Options

={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.

...

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!