Why COUNTIFS can't use a function in CRITERIA RANGE

Question

I'm trying to put MID into COUNTIFS criteria, but I understood COUNTIFS doesn't accept this. I'd like to understand the main reason of this.

Here it is an example. In column A I have string formatted values, that look like dates. I want to count the number of occurencies of values starting with a specific string, i.e. starting by "06-DEC-17"

      A
1     06-DEC-17 08:03:51,000000000
2     06-DEC-17 08:45:36,000000000
3     06-DEC-17 16:59:52,000000000
4     07-DEC-17 13:01:11,000000000
5     07-DEC-17 18:04:58,000000000
6     08-DEC-17 17:35:12,000000000

The formula #1 works, but formula #2 doesn't. Why?

Formula #1

=COUNTIFS(A1:A6;"06-DEC-17*")

Formula #2

=COUNTIFS(MID(A1:A6;1;9);"06-DEC-17")

I read here Possible to use COUNTIF with a function on the range's values? that any formula inside COUNTIFS criteria range returns an array, while COUNTIFS expects to deal with an EXCEL RANGE instead.

Is it this the main reason why any formula like #2 will not work, as any funcion will not output a RANGE but an array of VALUES?

barry houdini barry houdini · Accepted Answer · 2018-01-11T19:00:13

You have essentially answered your own question but, yes, some functions, including all of the "IFS" family of functions like SUMIF,COUNTIFS, AVERAGEIFS etc. all require ranges in the range part.

Other function that fall in to this category are COUNTBLANK and OFFSET (for first argument)

This also explains why those functions can't be used to extract data from closed workbooks, because data is extracted from closed workbooks as an array.

There are some other functions that work the other way round, e.g. they will accept arrays but not ranges, e.g. WEEKNUM function and most of the old "Analysis ToolPak" functions.

Why COUNTIFS can't use a function in CRITERIA RANGE

1 Answers