Solved

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

Posted on 2013-01-09
7
374 Views
Last Modified: 2013-01-09
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
Comment
Question by:Tocogroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38758906
Try with wildcards....

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

Expert Comment

by:Shanan212
ID: 38758908
=COUNTIFS($B:$B,$E3,$C:$C,"*Error1*")
0
 
LVL 24

Expert Comment

by:Steve
ID: 38758940
For multiple :

=SUM(COUNTIFS($B:$B,$E3,$C:$C,{"*Error1*","*Error2*"}))
(entereed with [ctrl]+[shift]+[enter])
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:Tocogroup
ID: 38759245
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
 
LVL 24

Expert Comment

by:Steve
ID: 38759274
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
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38759359
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 Closing Comment

by:Tocogroup
ID: 38759670
Many thanks NB_VC.
That was just the job.
Regards
Toco
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question