Need expert helpâ€”fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Hello,

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

Should be entered using [ctrl]+[shift]+[enter]

The

Then the

The

=SUMPRODUCT(--(FIND(" ",TRIM(A4:A15)&" ")<LEN(TRIM(A4:A15))))

Please correct me if I'm wrong but it seems that

=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

=SUM(N(YEAR(A2:A100)=2013)

If either or both of the formulas in the two other posts (by

Also, many thanks to

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

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.

There's just been an enormously long thread at MrExcel where one member has insisted that using VALUE function is the

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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