Is there a function which adds up both blank cells and cells with text? I
know I can do it with countA + countblank, but one function would work better.

thanks much for your help.

RK

RK,

That should be a constant....unless your range is dynamic. But, to answer
your question, there is no single worksheet function that will work the way
you want it to, unless you have a single row or column, in which case
COLUMNS or ROWS will work as well.

Bernie


"rk0909" wrote in message
...
Is there a function which adds up both blank cells and cells with text?
I
know I can do it with countA + countblank, but one function would work
better.

thanks much for your help.

RK

If the range of cells you want to count is in a single column, you can just
subtract the row numbers (plus 1):

=ROW(B20)-ROW(B5)+1

To count all cells in any range, you can use this user-defined function.
Paste it in a VBA module:

Public Function CountCells(Target As Range) As Long
CountCells = Target.Cells.Count
End Function

You would call it like this:
=CountCells(B5:B20) or
=CountCells(test) if 'test' is a named range.

If you are new to VBA, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

If you want to count all the cells in a range which do not have numbers in
them then an array formula like this should work:

=SUM(IF(ISNUMBER(A5:B20),0,1))

For an array formula, instead of Enter, you have to press Ctrl + Shift +
Enter. If you do it right, Excel will add curly braces around the formula.
You can then copy/drag it like other formulas, but if you edit it, you have
to use Ctrl + Shift + Enter again.

Hope this helps,

Hutch

"rk0909" wrote:

Is there a function which adds up both blank cells and cells with text? I
know I can do it with countA + countblank, but one function would work better.

thanks much for your help.

RK

Try this:

=SUMPRODUCT(--(A1:C12=0))


"rk0909" wrote:

Is there a function which adds up both blank cells and cells with text? I
know I can do it with countA + countblank, but one function would work better.

thanks much for your help.

RK