6

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
  • 744
  • 3
  • 12
  • 36
  • You could try naming each of the non-contiguous ranges. – BillDOe Jul 10 '15 at 18:21
  • 2
    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

2 Answers2

1

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
  • 1,473
  • 12
  • 14
  • Antony is correct I have used this method in the past to determine empty or blank cells – Matt Oct 31 '16 at 01:32
-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
  • 461
  • 4
  • 24