[Solved] Trying to Simplify a Formula

[daveg7]

Please upload the spreadsheet.

The formulas in column F (under "Results") work perfectly, except that I hope there's a simpler formula that would do the job.

Any ideas (I wonder if there's a way to adapt something like one of the lookup functions)?

[Zizi64]

Formula Question Zizi64.ods

(16.94 KiB) Downloaded 113 times

[daveg7]

Beautiful, clear formula.

THANKS!
---

However, I added many columns to the original sheet, and again made your new formula long, as follows:

=IF(NOT(ISNA(MATCH(Q527;O$5:O$5001;0)))+NOT(ISNA(MATCH(S527;O$5:O$5001;0)))+NOT(ISNA(MATCH(U527;O$5:O$5001;0)))+NOT(ISNA(MATCH(W527;O$5:O$5001;0)))+NOT(ISNA(MATCH(X527;O$5:O$5001;0)))+NOT(ISNA(MATCH(Y527;O$5:O$5001;0)))+NOT(ISNA(MATCH(Z527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AA527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AB527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AC527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AD527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AE527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AF527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AG527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AH527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AI527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AJ527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AK527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AL527;O$5:O$5001;0)))+NOT(ISNA(MATCH(AM527;O$5:O$5001;0)));"Y";"N")

It now refers to cells in cols Q, S, U, and W:AM. I could, ideally, combine all the columns into one range Q:AJ and then hope someone's imagination (and knowledge of the syntax) is equal to simplifying the formula yet again.

[acknak]

Another approach: in F4: =SUMPRODUCT($G4:$I4=E$4:E$13)

The formula gives the number of matches. To display this as Y or N, change the cell format to: "Y";"N";"N"

Drag-fill down to the cells below.

[daveg7]

Wow! This works, and it's very simple.

Thanks!

Also, I tried separating the columns into two ranges and the following worked just fine:

=SUMPRODUCT($G4:$H4=E$4:E$13)+SUMPRODUCT($K4=E$4:E$13)