[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

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
0
Steve_Brady
Asked:
Steve_Brady
  • 4
  • 3
  • 3
  • +1
9 Solutions
 
Saqib Husain, SyedEngineerCommented:
Try something like

=SUMPRODUCT(--(FIND(" ",A2:A100&" ")>1))
0
 
SteveCommented:
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
 
barry houdiniCommented:
Hello Steve,

Try using wildcards in COUNTIF, I.e.

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

regards, barry
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Saqib Husain, SyedEngineerCommented:
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
 
Steve_BradyAuthor Commented:
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
 
barry houdiniCommented:
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
 
SteveCommented:
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
 
barry houdiniCommented:
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
 
SteveCommented:
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
 
barry houdiniCommented:
**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
 
Steve_BradyAuthor Commented:
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
 
Steve_BradyAuthor Commented:
Thanks for the multiple responses & dialog
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now