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
  • 794

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,563
-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
  • 471