Count with merged cells

  • Thread starter Thread starter Excelerator
  • Start date Start date
E

Excelerator

I need to count the number of blank cells, or lacking that, the number of
cells that contain either numbers or alpha characters in a range. Some cells
are merged in the range, and COUNTBLANK "counts" the merged cells as having
blanks. That is, if two cells are merged and something is entered in the
merged cell, the count functions "see" the second cell as blank. If three
cells are merged, the functions read two of the cells as blank. COUNT,
COUNTIF, and COUNTA will only return a value of "1" when the merged cells
contains data. Is there a way to count all the cells in the range that are
blank WITHOUT including the "blank" second, third, and so on cells in merged
cells among the count? Or is there a way to count the cells that have data
and include the second, third, etc. merged cells among the count?
 
hi, !
... if two cells are merged and something is entered in the merged cell
the count functions "see" the second cell as blank...
Click to expand...

FWIW over "merged" CELLS, the cout functions DO NOT "see" the other cells as blank
THERE ARE (really) blank cells, since "merged cells" ARE NOT a "single" cell, but a range of CELLS

so, in order to not include "the other cells" (from a merged cells range) in a countblank function...
do NOT include "the other cells" within the function/formula, or even better, DO NOT USE "merged cells"
or... be aware that "merged cells" are not "single cells" (?)

hth,
hector.
 
Thank you, Hector. Unfortunately, I'm adding a function to existing
spreadsheet that contain lots of merged cells. It is possible to count the
number of merged cells, and then adjust the value from COUNTA or COUNTIF or
COUNTBLANK to take them into account, but its a big pain to do that. If I
could just count the blank cells without having to adjust for the merged
cells, life would be good. Is there a way to put something into the second,
third, etc., cell in an array of merged cells so they won't be blank? Or a
way to define the cells I'm counting andto incorporate it into one of the
COUNT functions to avoid including the merged cells in the count?
 
Back
Top