Count blank cells and cells with text

[rk0909]

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

 

[Bernie Deitrick]

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

 

[Tom Hutchins]

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/2008/03/09/how-to-use-someone-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

 

[Teethless mama]

Try this:

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