If function for a criteria along multiple cells

Occasional Contributor

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!

1 Reply


In E1:


In E7, depending on what you want, either:




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