1

I'm trying to count how many non-empty row I have in a range that is non-contiguous. I know it's easy to do if I create additional column that checks if a row is empty and then count that, but I'd like to do all that in a single cell. Here's an example table: enter image description here

So here my ranges would be "A1:C10" ; "E1:F10" ; "H1:H10" and I want to count the rows that are empty in these ranges. (So in this case the answer would be 2: rows "5" and "8") Note that there might be data in column "D" and "G" that I need to disregard in the evaluation. Also in the real data I have a lot of rows, but the number of ranges (columns)that is need to evaluate at a time are not the much so it is acceptable to enter the ranges in the formula by hand.

I've found this post and tried the formula in the last answer, but as far as I know I can't use non-contiguous range with COUNTBLANK, so I couldn't modify to work for me.

I tried this (entering as array formula) but it didn't work:

=SUM(IF(AND((COUNTA(OFFSET(E5,ROW(1:13)-1,0,1,4))=0),(COUNTA(OFFSET(I5,ROW(1:13)-1,0,1,1))=0), (COUNTA(OFFSET(C5,ROW(1:13)-1,0,1,))=0)),0,1))

Does anybody know a good formula or method for this?

Razero
  • 321
  • 1
  • 4
  • 16

1 Answers1

1

Use MMULT as it is not volatile like OFFSET.

=SUM(--(MMULT((A2:H11<>"")*(COLUMN(A2:H11)<>4)*(COLUMN(A2:H11)<>7),TRANSPOSE(COLUMN(A2:H11)^0))=0))

Depending on one's version of Excel it may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Nice! It works with the example, but what if I have more that one "unnecessary" column between two ranges? Do I need to add more `*(COLUMN(A2:H11)<>4)` with the correct col index to exclude all? Could you perhaps also explain a little how it works, so it'd be easier to customize it? – Razero Feb 01 '20 at 21:48