SOLVED

Countifs Formula

Copper Contributor

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

` `

5 Replies

Re: Countifs Formula

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

Re: Countifs Formula

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

Could it be because I merged the cells?

Re: Countifs Formula

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.

best response confirmed by MathijsSerlie (Copper Contributor)
Solution

Re: Countifs Formula

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

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

Re: Countifs Formula

Hi Riny,

Thanks for your help, this works

1 best response

Accepted Solutions
best response confirmed by MathijsSerlie (Copper Contributor)
Solution

Re: Countifs Formula

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

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