Forum Discussion

MathijsSerlie's avatar
MathijsSerlie
Copper Contributor
Jan 26, 2024

Countifs Formula

Hi,

 

I'm stuck and need help.

I'm looking for a countifs formula that searches in a range for multiple criteria. I tried it with some exampes I found online but without a positive result.

The formula has to search for Partial text.

 

Range = E22:R22 count if cells contains WD and/or 08

cell E22 contains 370WD01 and Cell R22 contains 3600804, Cell S22 contains the result (2)

 

I hope this all makes sense

 

Kind regards,

Mathijs

 

 

  • MathijsSerlie 

    I suspect you need to use the semi-colon to separate the items of the 'search' array.

    =COUNT(SEARCH({"WD";"08"},E22:R22))

  • MathijsSerlie 

    =COUNT(SEARCH({"WD","08"},E22:R22))

     

    This formula returns the intended result in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

     

    • MathijsSerlie's avatar
      MathijsSerlie
      Copper Contributor

      OliverScheurich 

      Thanks for your quick reply.

      Unfortunately it doesn't work. I use Office 365 and/or Microsoft Excel online.

      Could it be because I merged the cells?

       

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        MathijsSerlie 

        I suspect you need to use the semi-colon to separate the items of the 'search' array.

        =COUNT(SEARCH({"WD";"08"},E22:R22))

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    MathijsSerlie 

    Alternatively, maybe this formula could be useful for you.

    =COUNT(FILTER(E22:R22, (ISNUMBER(SEARCH("WD", E22:R22))+ISNUMBER(SEARCH("08", E22:R22)))>0))

    This formula should work in both Office 365 Excel and Excel Online. If you have merged cells within the range, please be cautious, as formulas might behave differently in merged cells. Ensure that the merged cells do not interfere with the logic of your formula.

Resources