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.
Column A is their ID in our database
Column B is the ID of a gift
Column C is the Month (numerical) that the gift was given
Column D is the formula that only works for the first line =MODE.SNGL(IFS($B:$B=$A2,$C:$C))
Similar formulas (not with MODE) has worked for me in the past, any thoughts?
Thanks
6 Replies
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.
Hii,
I have tried solve the problem through 2 steps:
1. I have created a pivot of your sheet
2. I have used match function to find out the month.
see Sheet 2 of the attachment
Thanks
Would reverting the IFS function to a simple IF sort it?
I would think summarizing by Pivot Table would be the quickest solution. You will get a unique list of [CnBio_ID] on rows and [Month] on columns, then you can just count the [Month]'s as values.
