SOLVED

# Filter rows from multiple column data

I currently have 5 columns with names I.E A, B, C, D, E and I want to be able to return the sum of the data (F1:AE23) for when those 5 names are duplicated. However, they may be in a different order I.E. A, C, D, B, E 4 Replies

# Re: Filter rows from multiple column data

=BYROW(A1:E23,LAMBDA(row,IF(AND(ISNUMBER(SEARCH("A",TEXTJOIN("",,row))),ISNUMBER(SEARCH("B",TEXTJOIN("",,row))),ISNUMBER(SEARCH("C",TEXTJOIN("",,row))),ISNUMBER(SEARCH("D",TEXTJOIN("",,row))),ISNUMBER(SEARCH("E",TEXTJOIN("",,row)))),SUM(OFFSET(row,0,5,1,26)),"")))

You can try this formula. However there should be an easier way to check if A, B, C, D and E occur in the first 5 columns in any order. best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Filter rows from multiple column data

=BYROW(A1:E23,LAMBDA(x,IF(TEXTJOIN("",,TRANSPOSE(SORTBY(TRANSPOSE(x),TRANSPOSE(x))))="ABCDE",SUM(OFFSET(x,0,5,1,26)),"")))

An alternative could be this formula which is easier to adapt if you want to check if e.g. A to Z occur in the first 26 columns in any order. # Re: Filter rows from multiple column data

Barring any anomalies in the data or anything different than what's been offered in the mock data, this might work for you (If you have access to LAMBDA).

We can simply the logic by taking 5 columns from the left of a given row.  Run UNIQUE by column and if the result is 5 (no dupes), then SUM the row.

``=LAMBDA(row,IF(COUNTA(UNIQUE(TAKE(row, , 5), 1)) = 5, SUM(DROP(row, , 5)), ""))``

Folded into BYROW:

``=BYROW(data,Total)``

# Re: Filter rows from multiple column data

This worked! Thank you for the help!