Forum Discussion
Issues with finding MODE when using IFS
Hello, I work for a nonprofit and we are trying to find what month is most common for specific individuals to give during. I have attached a list with important personal information removed. ...
IFS() returns #N/A error if no one condition is met (e.g. =IFS(2=3,10) returns #N/A). For some rows you definitely have #N/A error with
IFS($B:$B=A2,C:C)thus entire formula returns #N/A error. To return something if main condition is not met it could be like =IFS(2=3,10, TRUE, "not met")
In your case if change IFS and entire formula as
=IFNA(MODE.SNGL(IFS($B:$B=A2,C:C,TRUE,"")),"no such ID")it works since MODE.SNGL() ignores texts. And we wrap entire formula with IFNA() for the cases when there are no ID from left column in the right one.
Please check attached.
I still do not see the point of using IFS for a single condition. The basic IF will return FALSE which is ignored by aggregation functions.
PeterBartholomew1 , I only tried to explain why initial formula doesn't work and how IFS works.