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 :)

Some_Guy

Posted 2015-07-10T18:04:57.010

Reputation: 684

You could try naming each of the non-contiguous ranges. – BillDOe – 2015-07-10T18:21:35.763

2Some 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 – 2015-07-10T19:43:04.053

That doesn't seem to work, BillOer. – Jason Clement – 2015-09-25T15:02:34.813

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 – 2016-10-12T17:58:05.787

Answers

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

Posted 2015-07-10T18:04:57.010

Reputation: 1 125

Antony is correct I have used this method in the past to determine empty or blank cells – Matthew Lozoya – 2016-10-31T01:32:26.127

-1

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.

Selkie

Posted 2015-07-10T18:04:57.010

Reputation: 429