Count with merged cells

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?
 
H

Héctor Miguel

hi, !
... if two cells are merged and something is entered in the merged cell
the count functions "see" the second cell as blank...

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.
 
E

Excelerator

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top