Count array cells that returned a result

D

Dennis

Using 2003 at home and 2000 at work

Would like to count the number of cells in a range that have a result from a formula.

The particular range of cells is filled with array formulae.

Thus, the cells are not "" because they have a formula.

But I would like to know how many cells, in the range of cells, where the formula produced a
displayed value.

I tried countif(A1:D20,not("")) (and other variations without success)

TIA Dennis
 
P

Peo Sjoblom

One way

=COUNTBLANK(A1:D20)-SUMPRODUCT(--(ISBLANK(A1:D20)))

will return the number of cells that have a "" produced by a formula

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
F

Frank Kabel

Hi RD
though this may work in most cases I wouldn't use this kind of syntax
as this could return wrong results based on your used range. In my
experience COUNTIF has problems with conditions like
=COUNTIF(rng,"")
or
=COUNTIF(rng,"<>")

at least prior to Excel 2003 (in this version there seems to be a fix
for this issue). Try these kind of formulas on a new, blank workbook
 
R

Ragdyer

Tried what you suggested Frank, and still obtained the correct results.
In fact, did it *ten* times, on ten new WBs, and all worked OK!

Could you give me a quick rundown on "used range"?
 
P

Peo Sjoblom

Open a new workbook in Excel prior to 2003, don't put anything in any cell
and use

=COUNTIF(A1:D20,"")

you should get 80 but you won't

in 2003 it is fixed

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
F

Frank Kabel

Hi
the error can only be seen in Excel prior to 2003.:
- open/create a new workbook
- don't do anything but enter the following formula in B1:
=COUNTIF(A1:A10,"")

This should give you 10 (but it won't)
 
A

Alan Beban

For xl2000, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,
=ArrayCountIf(A1:A10,"") will give a count of empty strings, and
=ArrayCountIf(A1:A10,"=") will give a count of the empty blanks.

Alan Beban
 
R

Ragdyer

OK, now I follow you guys.

But ... I'm still interested in your comment:
<<" this could return wrong results based on your used range">>

Could you please elaborate on "used range" as pertaining to the above
context.

Actually, when I "used" a range *first*, everything turned out fine!
 
P

Peo Sjoblom

If you for instance set aside a range like A1:D500 and a formula to count
blank cells and
it will be filled from top down, let's say that you use the formula

=COUNTIF(A1:D500,"")

then you fill from the top down to row 20, so you
fill A2:D20, 2003 will return 1920 but XP
returns 0



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
F

Frank Kabel

Hi
COUNTIF (and SUMIF) seems to have these problems if the used range is
smaller than the range in your function. e.g. If you use the function
=COUNTIF(A1:A10,"")
you'll receive an error. But now try filling out this 10 cells and
delete the entries afterwards. Now the function should return the
correct result (as the used range now is A1:A10)
 
R

Ragdyer

That's why the ten times I tried it after your first "caveat" worked for me!
I filled the range before entering the formula.

Thanks for the heads up guys.
 
Top