Filter rows from multiple column data

New Contributor

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, EMock Data .png

4 Replies




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.

sum from multiple columns.JPG

best response confirmed by Hans Vogelaar (MVP)




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.

sum multiple columns lambda.JPG


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:




This worked! Thank you for the help!