Solved

Use a function as the criteria in the Excel =COUNTIF() function

Posted on 2013-06-04
12
841 Views
Last Modified: 2013-06-19
Syntax:  =COUNTIF(range, criteria)

Hello,

How do you insert a function rather than a value for the criteria in the Excel =COUNTIF() function?

For example, suppose you have a list of the names of cities in the range A2:A100. And suppose you want to count the number of city names in that range which have more than one word.

I realize that you could use the following formula pasted down in column B to identify cities with one or more spaces:

    =ISNUMBER(SEARCH(" ",A2))
 
and then use the column B results as the criteria in the =COUNTIF() function as follows:

    =COUNTIF(B2:B100,"TRUE")

However, is there a way to include the desired criteria directly in the =COUNTIF() function so that a second column need not be tied up?

Thanks
0
Comment
Question by:Steve_Brady
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 83 total points
ID: 39221194
Try something like

=SUMPRODUCT(--(FIND(" ",A2:A100&" ")>1))
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 167 total points
ID: 39221259
You can use:

=SUM(N(ISNUMBER(SEARCH(" ",A1:A10))))
Should be entered using [ctrl]+[shift]+[enter]

The N() converts the TRUE or FALSE to 1 or 0
Then the SUM() totals all of them up.
The [ctrl]+[shift]+[enter] makes Excel handle each row individually as an Array.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 39221320
Hello Steve,

Try using wildcards in COUNTIF, I.e.

=COUNTIF(A2:A100,"* *")

regards, barry
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 83 total points
ID: 39221355
My first formula was wrong. Here is another formula which allows for leading or trailing spaces.

=SUMPRODUCT(--(FIND(" ",TRIM(A4:A15)&" ")<LEN(TRIM(A4:A15))))
0
 

Author Comment

by:Steve_Brady
ID: 39223171
Thanks for the responses.

Please correct me if I'm wrong but it seems that The_Barman's post contains the only solution:

    =SUM(N(ISNUMBER(SEARCH(" ",A1:A10))))   {cse}

that is easily adaptable to other functions. For example, suppose that in place of city names, the range in my example (A2:A100) consists of random dates and your goal is to determine how many of those dates lie within the year 2013.

Modifying The_Barman's solution would result in the formula:

    =SUM(N(YEAR(A2:A100)=2013))   {cse}

If either or both of the formulas in the two other posts (by ssaqibh & barryhoudini) are easily modifiable in the same way, please explain.

Also, many thanks to The_Barman for the additional comments. Those are most helpful and appreciated—especially the third one re regarding Array formulas. The way you worded that caused something to click in my head and I suddenly feel like I understand Array formulas much better.  :)
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 39223468
Hello Steve,

What you are talking about is modifying the range within COUNTIF. That isn't possible because when you use YEAR(A2:A100) the result is an array rather than a range....and COUNTIF won't accept arrays in the range arguments (and nor will any of the other "IF" functions like SUMIFS/AVERAGEIF etc.)

So, yes, whenever you need to modify the range with a function you need to use another approach as per The_Barman's suggestion, either array formula or often you can use SUMPRODUCT as Saqib says, e.g. here you can use

=SUMPRODUCT(ISNUMBER(FIND(" ",A2:A100))+0)

using SUMPRODUCT normally means you don't need CSE....and personally I would usually use a "co-ercer" like +0 or *1 rather than N function.

For the YEAR example you can do the same, e.g.

=SUMPRODUCT((YEAR(A2:A100)=2013)+0)

although bear in mind that SUMPRODUCT and array formulas with large ranges are usually much less efficient than SUMIFS/COUNTIFS so if you can get a version to work with those latter functions it might be quicker for large ranges, e.g. you can use COUNTIFS to count dates within a particular period by specifying the start and end points like this for 2013

=COUNTIFS(A:A,">="&DATE(2013,1,1),A:A,"<"&DATE(2014,1,1))

regards, barry
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Assisted Solution

by:Steve
Steve earned 167 total points
ID: 39223668
Barry, as ever I would always bow to your Ninja skills with the formula.. but why use a coerce such as "--" or "+0" or "*1" rather than N() when converting a Boolean response? Is this preference or is there a deeper reasoning?
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 39223751
Hello (Barman) Steve,

It's pretty much a personal preference I suppose, although there is some logic to it - I first started using Excel when the nested function limit was 7 (it's 64 now in Excel 2007 or later) so there was often a need to minimise the number of function calls to keep within that limit.

Also it was (and still is) the default "method of choice" at several forums at which I participate, e.g. MrExcel (although many prefer -- while my preference is +0)

I doubt if it makes much practical difference now......

regards, barry
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 167 total points
ID: 39223775
Indeed, in the past I would likely have used -- but in more recent times 2010+ I have tended towards the use of N() when refering to Booleans. If this was a LEFT() to return a text which was to be converted I think I would still use --. I just wondered if you were going to enlighten me to a more practical reasoning :)

I too also take part in other sites (not too much with time constriants) so have seen the uses in practice, I think it was mrExcel that got me off *1 to the double negative.

ATB
Steve.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 39223926
**Off topic alert**

There's just been an enormously long thread at MrExcel where one member has insisted that using VALUE function is the only way that should be used to convert "text numbers". I didn't participate but my view, as always, is that it's best to educate yourself on all the possible methods and usages as far as it's practical for you, then make your own choice.

I understand that -- may have been proved to be very marginally quicker but I think it was Harlan Grove (who's almost an excel forum legend) who concluded that -- was better purely because you could type it more easily (without using SHIFT as per * and +) but personally I think it looks ugly and it confuses the hell out of many people (although I'm sure that +0, N and *1 are no less confusing to the same people)

regards, barry
0
 

Author Comment

by:Steve_Brady
ID: 39224198
Thanks for the great explanations & dialog. It's going to take me a bit of time and reading to really understand it but it's always exciting to research a topic which relates to a current problem or issue.

It is also undoubtedly going to spawn some additional EE threads, one of which I posted just now:

    Need help combining 4 Excel formulas please

The new question gives a broader and more detailed explanation of what I was actually after in the present thread. I probably should have included those details here.

Thanks again
0
 

Author Closing Comment

by:Steve_Brady
ID: 39259811
Thanks for the multiple responses & dialog
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now