Forum Discussion
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
I suspect you need to use the semi-colon to separate the items of the 'search' array.
=COUNT(SEARCH({"WD";"08"},E22:R22))
- OliverScheurichGold Contributor
=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.
- MathijsSerlieCopper Contributor
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_EekelenPlatinum Contributor
I suspect you need to use the semi-colon to separate the items of the 'search' array.
=COUNT(SEARCH({"WD";"08"},E22:R22))
- NikolinoDEGold Contributor
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.