MAXIF, MINIF, AVGIF Formula Capability

Trang Nguyen

It would help to have the IFS functionality and combined statistical functionality MAXIF, MINIF, AVGIF, MAXIFS, MINIFS, AVGIFS similar to the current SUMIF and COUNTIF.

IFS can shorten multiple nested IF statements and easier to formulate, read and understand when referencing multiple conditions.

The combined statistical functionality will match the already implemented SUMIF(s) and COUNTIF(s). This functionality will reduce the unnecessary "helper" columns or the only workaround option of using MAX(COLLECT/JOIN when columns that are not adjacent. Without the ability to UNHIDE individual columns or between a set of columns vs all the columns, this makes the design process for best user experience VERY time consuming when having to hide and unhide fields just to accommodate/update 1 field.

Robyn Pindell

I second this!!!!!!!!!! It's so needed!

PaytonMoore1

Is an averageifs formula being worked on?

Bryce Jablonski

1000% agree with this. I am in the final stages of a project and having these options within Smartsheet would have made things way easier. I worked out the formulas in Excel since that is more of my area and then converted to Smartsheet and they did not convert as easily with some.

Bryce Jablonski

Who else is on board with having IFS formulas added into Smartsheet as an option? I think it would be beneficial overall for most users.

Paul Newcome

How would that function differently in relation to a nested IF other than not putting "IF(" in between each set?

Paul Newcome

How would (for example) MAXIF or MAXIFS function any differently than MAX(COLLECT(…….))?

Bryce Jablonski

The only reason I suggest it is because having more experience in Excel. The project I was just wrapping up was my first time working in Smartsheet.

I worked out the formulas in Excel first and then moved them over to Smartsheet. The only one that was not working was the IFS formula and the nested IF formula was not working either because it was being overloaded.

It would just be beneficial in my mind to have that IFS formula for better functionality across the board, so it aligns more with those starting in Smartsheet for the first time who have more experience in Excel.

Paul Newcome

If you'd like to post the nested IF that you had challenges with, maybe we could help with it. I'm not sure what you mean by "overloaded". Was is a specific error, outputting something unexpected, taking too long to calculate, or something else?

The IFS in Excel works pretty much the same as a nested IF in that it works from left to right and stops on the first true value. Generally speaking, the only time IFS is better than nested IF (as far as functionality goes at least) is that IFS requires fewer characters to be typed.