CODEWITHSUNDEEP
excelexcel-formula
OliAK
OliAK

Reputation: 73

COUNTIF() for non-contiguous ranges

I am trying to use COUNTIF function for non-contiguous ranges. I have used the following formula and it works.

=SUM(COUNTIF(INDIRECT({"Z65";"Z66";"Z68";"Z71";"Z72"});"2"))

However, it is not using cell reference. Therefore whenever I am adding an additional row then I have to change it manually. Is it possible to use COUNTIF() for non-contiguous ranges with cell reference? 

Upvotes: 0

Views: 280

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Yes, you will need to add INDEX and SEQUENCE:

=SUM(COUNTIFS(INDIRECT(INDEX(Z65:Z1040000;SEQUENCE(COUNTA(Z65:Z1040000))));2))

enter image description here

Upvotes: 1

Related Questions