n=? in each column

[CatPEG]

I want to determine the number (n) of entries (each is a number: 0-5) in each
column
(Rows 2 to 143, Columns B through K)

The formula I used to get a total for Column B: =COUNT(B2:B143)
gives me an answer in Row 144 that says there are 43 entries.

However, if I manually count the number of entries, the answer is 44.

Same thing happens for Column C: formula answer is 60, but my manual count
of data entries is 71.

What am I doing wrong? I will appreciate any help you can give me!

 

[JMB]

Perhaps some of the numbers are formatted as text or have a text qualifier.
Try formatting your data as general. Then enter the number 1 in an empty
cell and copy this cell. Then select your data and click Edit/Paste Special
and select "multiply".

 

[CatPEG]

THANKS, JMB! Your solution works, and I get the correct total no. The only
problem is that there are a lot of blank cells in between the entries, so you
can't just special paste a column, you have to do each entry or sections
together at the same time...otherwise I end up with a bunch of 1's that don't
belong on the data sheet. Do you happen to have a solution that would be
quicker?

 

[MartinW]

Hi CatPEG,

Use the same process but copy a 'blank' cell
and Paste>Special and select "add"

Make sure the cell you copy is truly a blank fresh cell
and not something that has had data in it and been deleted.

HTH
Martin

 

[JMB]

Martins suggestion is the easiest - so give that a go.

But, just for the sake of academics - you could copy the 1, then click
Edit/GoTo/Special, select constants (numbers and text), then click
Edit/PasteSpecial/Multiply.