Hi all,

Hoping somebody out there can offer an explanation (or, failing that, a strong hypothesis) as to the large discrepancy in performance of the two seemingly related functions COUNTIFS and SUMIFS when arbitrarily large ranges (e.g. entire columns) are passed as the criteria_range parameters. Specifically, I am looking for potential explanations as to the process by which COUNTIFS determines the used range over which to calculate.

This question arose on the back of the discussion in the latter posts of this thread:

https://www.excelforum.com/excel-for...ing-value.html

As a comparison for testing purposes: in a blank workbook, of the following formulas, both copied to a range of 50,000 cells:

=COUNTIFS($A:$A,1,$B:$B,2,$C:$C,3,$D:$D,4)
=SUMIFS($E:$E,$A:$A,1,$B:$B,2,$C:$C,3,$D:$D,4)

the former takes 24 seconds for a full worksheet calculation, whereas the latter takes 1 second.

It is assumed that SUMIFS achieves such efficiency via calculating over the used range only, and that this used range is determined via inspection of the sum_range only. This makes perfect sense, since any data present in the criteria_ranges in rows beyond that containing the last entry within the sum_range will in any case be redundant.

Since COUNTIFS lacks the advantage of having a single range which can be used to determine with certainty the used range over which to calculate, I would expect it to perform slightly worse than SUMIFS. But not this much worse. Even a rudimentary approach which involved calculating the last-used cell in each of the criteria_ranges and then taking the largest of these should not amount to a 24-fold decrease in performance. What's more, the number of criteria_ranges does not appear to play a role:

=COUNTIFS($A:$A,1,$B:$B,2)

and

=COUNTIFS($A:$A,1,$B:$B,2,$C:$C,3,$D:$D,4,$E:$E,5,$F:$F,6,$G:$G,7,$H:$H,8,$I:$I,9,$J:$J,10)

for example, also both take 24 seconds.

(Though note that:

=COUNTIFS($A:$A,1)

with just a single criteria_range, calculates almost instantaneously.)

So what is happening here? What algorithm is COUNTIFS employing to detect the used range over which to calculate? Note that it is a strong assumption that there must be some such algorithm in place:

=SUMPRODUCT(0+($A:$A=1))

for example, which is known to not operate over the used range only (rather, all cells within the range passed), practically crashes Excel when applied to 50,000 worksheet cells.

Any information/insights greatly appreciated. VBA experts/gurus alike, if you are reading this.

Regards