Is there such a formula?

J

Julie P.

Hi, I use Excel 97. Is there a formula which will allow me to automatically
count the number of cells in a range that have content?

For example, take this screenshot:

http://mallology.lunarpages.com/excel.gif

If I wanted to take all the cells between Columns F inclusive and O
inclusive, but below Row 2, and find out the number which have an "X" or
whatever marked in them, what should I do?

Thanks!

Julie
 
J

Julie P.

JE McGimpsey said:
One way:

=COUNTIF(F2:O1000,"*x*")
Click to expand...


Hi again! I guess you read all of the Excel newsgroups. :)

I tried it and it worked! The only problem though is sometimes I also use
"y" instead of "x". I suppose I could do two separate formulas though, one
for "x" and one for "y", and then just add the two.

J.
 
D

Daniel.M

Hi Julie,
I tried it and it worked! The only problem though is sometimes I also use
"y" instead of "x". I suppose I could do two separate formulas though, one
for "x" and one for "y", and then just add the two.
Click to expand...

Or put the x or y in a cell (say F2) and refer to it in the formula, as in :

=COUNTIF(F2:O1000,"*" & F2 & "x*")

Regards,

Daniel M.
 
J

Julie P.

Daniel.M said:
Hi Julie,


Or put the x or y in a cell (say F2) and refer to it in the formula, as in :

=COUNTIF(F2:O1000,"*" & F2 & "x*")
Click to expand...

Hi Daniel, thank you. I am not sure though if I understand the details of
what you mean. I generally have "x"'s and "y"'s scattered throughout the
range, so how does the "F2" come into play here?
 
B

Biff

Julie,

If you want to count the number of cells in that range
that have *anything* in them:

=COUNTA(F2:O1000)

Biff
 
J

Julie P.

Biff said:
Julie,

If you want to count the number of cells in that range
that have *anything* in them:

=COUNTA(F2:O1000)
Click to expand...


Biff, thanks so much. Yes, that formula worked perfectly!

Julie
 
Top