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