Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

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

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
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve_Brady

ASKER

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 ssaqibhbarryhoudini) 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.  :)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Thanks for the multiple responses & dialog