Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2013-06-04
Medium Priority
1,026 Views
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
[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
• 4
• 3
• 3
• +1

LVL 43

Assisted Solution

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

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

LVL 24

Assisted Solution

Steve earned 668 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

barry houdini earned 1000 total points
ID: 39221320
Hello Steve,

Try using wildcards in COUNTIF, I.e.

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

regards, barry
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 332 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

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 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.  :)
0

LVL 50

Accepted Solution

barry houdini earned 1000 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

LVL 24

Assisted Solution

Steve earned 668 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

barry houdini earned 1000 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

Steve earned 668 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

barry houdini earned 1000 total points
ID: 39223926

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

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

ID: 39259811
Thanks for the multiple responses & dialog
0

## Featured Post

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
###### Suggested Courses
Course of the Month10 days, 1 hour left to enroll

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

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