# Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.

``````=Countblank({A1,A4:A6,A112:127,B29:B38})  etc.
``````

Essentially, I want to refer to multiple locations where you would normally type a contiguous range. Non-VBA preferred, if that is indeed possible.

I know I can use

``````=Countblank(A1)+Countblank(A4:A6)+Countblank(A112:127)+Countblank(B29:B38)
``````

in this case, but it isn't ideal for obvious reasons.

Thanks everyone :)

• You could try naming each of the non-contiguous ranges. – BillDOe Jul 10 '15 at 18:21
• Some aggregate functions support this and others do not. You have little recourse if it is not supported. If you want to do this, you can define a UDF that takes a parameter array and outputs the result for a discontinuous range. That requires VBA though. – Byron Wall Jul 10 '15 at 19:43
• That doesn't seem to work, BillOer. – Jason Clement Sep 25 '15 at 15:02
• Why are you doing this? Can't you have an additional column that defines whether or not a column should be counted? There should be a way from inferring the logic from the data rather than having it hardcoded in the formula, for many reasons. – airstrike Oct 12 '16 at 17:58

By using the INDIRECT function you can create an array of ranges then use with the COUNTBLANK enclosed in SUM should give the required result.

``````=SUM(COUNTBLANK(INDIRECT({"A1","A4:A6","A112:A127","B29:B38"})))
``````

Alternative to COUNTBLANK you can use empty COUNTIF criteria.

``````=SUM(COUNTIF(INDIRECT({"A1","A4:A6","A112:A127","B29:B38"}),""))
``````
• Antony is correct I have used this method in the past to determine empty or blank cells – Matt Oct 31 '16 at 1:32

I'm not completely comfortable with this part of excel, but I know the theory well enough to point you in the right direction. Hopefully someone else that knows the area better can give a more detailed response.

The functions themselves don't support what you want. However, you can create your own functions in excel, using VBA. I believe it's the function command, and the way I'd set it up is something like

Function - defining

SUMIFM(Criteria, Range1, [Range2]...) = Sumif(Range 1, Criteria 1) + [Sumif(Range 2, Criteria 1)]...

Then, whenever you need to use a sumif over multiple ranges, you can use a sumifm.

It wouldn't surprise me if something like that was already created and in a library somewhere, meaning you wouldn't have to write it yourself. I wouldn't be surprised either if there were many, many functions similar to that in a library, which you could aggregate and append to your excel.