How to use the COUNTIF function in Excel that refers to a named range that is non-rectangular in shape.

Question

My issue is summed up in this example from Excel.

Answer 1

If you have selected C2:H3 and G4:H5 or you have selected C2:F3 and G2:H5 and you have formed a named range with the name ATestRange, then the COUNTIF function cannot work, since this function has in its syntax: range and criteria.

If the named range is not given as a rectangular shape, then the COUNTIF function does not consider the range as a single one, but as multiple ranges and for this reason it cannot work to make the desired calculation.

In such a case you can use this formula in cell K2:

=SUM(--(TOCOL(ATestRange, 1) = J2))

HTH

Answer 2

Hey there!

COUNT just looks for numbers, and Microsoft made it flexible to take multiple ranges.

COUNTIF must apply the condition to every cell in the range, and Excel’s implementation only allows one contiguous block per condition.

Try this (assuming your value range starts from A1):

=COUNTIF(Sheet1!$A$1:$D$2,2) + COUNTIF(Sheet1!$D$5:$E$5,2) +COUNTIF(Sheet1!$E$4,2)

Answer 3

Provided you are on a modern Excel version that supports the LAMBDA functions you try this:

The formula that returns the count (in K2) of the criterion (in J2) is:

=REDUCE(0,TOROW(testrange),LAMBDA(i, a, i + (a=J2)))