Countifs Formula

[MathijsSerlie]

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

[#2]

@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.

[#3]

@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?

 

 

[#4]

@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.

[#5 · Accepted solution]

@MathijsSerlie 

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

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

[#6]

@Riny_van_Eekelen 

Hi Riny,

Thanks for your help, this works