Is there such a formula?

[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

 

[JE McGimpsey]

One way:

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

 

[Julie P.]

One way:

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

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.

 

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

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.

 

[Julie P.]

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*")

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?

 

[Biff]

Julie,

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

=COUNTA(F2:O1000)

Biff

 

[Julie P.]

Julie,

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

=COUNTA(F2:O1000)

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

Julie