I am trying to extract the most common occurrence of a value (string) in a particular column before a particular date. I would like to find the most occurring value in cells F1:F that occurred before the date in cell A1. Cell A1 uses a formula to find the start of the workweek.
I have used the following formula, but it is not providing the results that I expected.
=UNIQUE(FILTER(F1:F,E1:E<$A$1,COUNTIF(E1:E,E1:E) = MAX(COUNTIF(E1:E,E1:E))))
Here is a screenshot of my Sample Spreadsheet:
The formula in cell C2 is returning the results "Yes" and "No". I want the result to be "No" since there are two occurrences of "Yes" before the date in A1 and four occurrences of "No" before the date in A1.
Can anyone point out the problem with my formula or a better formula to extract the desired information? I have tried tweaking this one every which way but to no avail.
>Solution :
You can try:
=let(
dates,tocol(E:E,1),
text,tocol(F:F,1),
counts,map(unique(text),lambda(each,counta(filter(text,text=each,dates<$A$1)))),
filter(unique(text),counts=max(counts)))
We are MAPping the count of text values in colF (subject to the date criterion) onto each unique text value in colF, then using the max value in this table to filter the list of unique text values.