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

  • 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
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"}),""))
| improve this answer | |
  • Antony is correct I have used this method in the past to determine empty or blank cells – Matt Oct 31 '16 at 1: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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.