*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

What you are talking about is modifying the

rangewithin 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(

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)

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(20

regards, barry