The COUNTIFS and COUNTIF functions can count the values in ranges which meet certain criteria. Although the COUNTIFS function accepts multiple range-criteria pairs, it doesn’t allow using different size ranges. You can overcome this with the help of the SUM and INDIRECT functions. In this guide, we’re going to show how to use the COUNTIFS Excel function with unequal ranges.
The trick is to supply ranges with an array and make the COUNTIFS or COUNTIF function to work with each range separately in a single formula.
Since there is no built-in support for array of ranges, you will need to use a workaround. This workaround is to use an array of strings that contain range references:
On this step, the INDIRECT function can evaluate references entered as strings and return the output range. For our example, the INDIRECT function returns an array of ranges.
With the array of ranges coming from the INDIRECT, the COUNTIFS or COUNTIF function works on each range in the array and returns an array as well.
The final step is to sum up the values in the final array. You can use a simple SUM function to finalize the calculation.
Although this formula is an array formula, you do not need to use the Ctrl + Shift + Enter key combination to enter this formula.
In summary, to use COUNTIFS Excel function with unequal ranges, use references as strings in combination with the INDIRECT function.
