Forum Discussion

benross94's avatar
benross94
Copper Contributor
Apr 18, 2023

If function for a criteria along multiple cells

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! 🙂

excel
Formulas and Functions

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 18, 2023

    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)

Resources