x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 382

# How do I include a substring search in an Excel 2010 COUNTIFS formula.

Hi All,

I'm trying to include a text search as one of the criteria in an Excel COUNTIFS function.

If I have the following formula.......

=COUNTIFS(\$B:\$B,\$E3,\$C:\$C,F\$2)

.....I want to replace F\$2 with a search within the cell text for a specific substring.

For example, if cell F\$2 contains the substring 'Error1' then the count will be incremented by 1.

I've attached a copy of my workbook.

Many thanks
Toco
Tests-and-Summary.xlsx
0
Tocogroup
• 2
• 2
• 2
• +1
1 Solution

Commented:
Try with wildcards....

=COUNTIFS(\$B:\$B,\$E3,\$C:\$C,"*"&F\$2&"*")
0

Commented:
=COUNTIFS(\$B:\$B,\$E3,\$C:\$C,"*Error1*")
0

Commented:
For multiple :

=SUM(COUNTIFS(\$B:\$B,\$E3,\$C:\$C,{"*Error1*","*Error2*"}))
(entereed with [ctrl]+[shift]+[enter])
0

Author Commented:
Thanks NB_VC. Your solution was the first.

One thing though, if I change the condition range from C:C to C2:C200, or to span TWO columns such as C:D then it returns a #VALUE error.

Any ideas ?
0

Commented:
You cannot span two columns for a single criteria range.

all criteria ranges should be the same size:
=COUNTIFS(\$B\$2:\$B\$200,\$E3,\$C\$2:\$C\$200,"*"&F\$2&"*")
0

Commented:
If you want to span 2 columns, you can add 2 separate COUNTIFS together

=COUNTIFS(\$B\$2:\$B\$200,\$E3,\$C\$2:\$C\$200,"*"&F\$2&"*") +COUNTIFS(\$B\$2:\$B\$200,\$E3,\$D\$2:\$D\$200,"*"&F\$2&"*")

or use SUMPRODUCT like this:

=SUMPRODUCT((\$B\$2:\$B\$200=\$E3)*(ISNUMBER(SEARCH(F\$2,\$C\$2:\$D\$200))))
0

Author Commented:
Many thanks NB_VC.
That was just the job.
Regards
Toco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.