Excel INDIRECT() with non-contiguous range ... only works with COUNTIF()?

Question

I need to use the Excel Indirect() function to reference a non-contiguous range. This (How to define a non continuous range in COUNTIF) answer gives an example.

In summary, the OP has two ranges, C1:C15 and A16. Each range contains either an A or a B, and the way to count the number of B's across these two ranges is

=SUM(COUNTIF(INDIRECT({"C1:C15","A16"}),"B"))

If I change all the B's to 1's and the A's to 0's, and change this formula slightly to

=SUM(COUNTIF(INDIRECT({"C1:C15","A16"}),"1"))

Then this still works ... BUT! this doesn't:

=SUM(INDIRECT({"C1:C15","A16"}))

I can work around this, but am I missing something fundamentally magic about the COUNTIF() function, that somehow influences the INDIRECT() function to behave as expected?

Answer 1

It's not possible to use a non-contiguous range in COUNTIF.

What's actually happening with the first formula is that COUNTIF is being fed with an array of (two) separate ranges and therefore the result is an array of the results of two counts, then SUM is used to sum the array.

If you actually have 1s and zeroes wouldn't you just SUM them with this formula

=SUM(C1:C15,A16)

If you are just trying to find why your last formula doesn't work then, yes, I think COUNTIF does work differently - it's able to handle an array of ranges while some other functions can't. This is common to the "IFS" family of functions, so SUMIF, for example, can do the same