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?
=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.
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 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. :)
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
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?
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......
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.
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:
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
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.
=SUMPRODUCT(--(FIND(" ",A2:A100&" ")>1))