If function for a criteria along multiple cells

[benross94]

Hello, please see this sheet.

As described in the sheet, in cell E1 I am looking for a formula that will populate with a given word if cell A1 contains that word, but cells B1:D:1 do not contain that word.

I am also looking for a formula in cell E7 that sums column F if a certain name referenced in column A is included anywhere in columns B:D.

Any help is much appreciated!

[#2]

@benross94

In E1:

=IF(AND(ISNUMBER(SEARCH("Jess",A1)),ISERROR(MATCH("Jess",B1:D1,0))),"Jess","")

In E7, depending on what you want, either:

=IF(ISNUMBER(MATCH(A7,B1:D1,0)),F1,"")

or

=SUM(IF((B1:B1000=A7)+(C1:C1000=A7)+(D1:D100=A7),F1:F1000))

(If you do not have Microsoft 365 or Office 2021, confirm the latter formula by pressing Ctrl+Shift+Enter)