Calculating amount of cells containing data...

  • Thread starter Thread starter Pheasant Plucker®
  • Start date Start date
P

Pheasant Plucker®

OK...following the swift and helpful replies to my last question
(suppliers/manufacturers take note:-) I wonder if someone could help me with
the following question please?

I have a column with some cells blank and others with various numbers in.

If I want to summarize by number the amount of cells that have numbers in
how do I do it?

For example this column is setup to monitor builders costs and only cells A1
and A2 currently have figures of [100] & [200] respectively.

The others are currently blank but may have numbers added at a later date.

At the bottom of the column I calculate the total figure =SUM(A1:A10) to
give me the total amount spent [300] but I also want to itemise the number
of jobs completed (indicated by data in cells A1 & A2 containing data) which
in the example above would be 2

This would have to automatically update to reflect any changes made by
adding say 300 in A3

My total spent would then increase to 600 and I would want the amount of
jobs completed to increment by 1 to display 3

Is this easy to achieve?

Thanks in advance for your help.
 
If you *don't* need to allow for inserting rows, just use plain
=COUNTA(A1:A10)

Rgds,
Andy
 
The solution Andy gave you is fine, but you can insert rows if the count
formula is in A12, with the total formula in A11. All you have to
remember is when inserting rows that these should be above row 10, i.e.
highlight row 10 and then insert row.

This way the the formulae are adjusted to the new range.

Hope this helps

Mike
 
Thanks for the quick response guys...

Although I don't intend to insert rows I can't rule it out in the future
so...is there another way to do it or were you being gentle with me as a
newbie to Excel? ;^)

Thanks & regards,
-pp-
 
were you being gentle with me as a
newbie to Excel? ;^)
Click to expand...

Not really, it's just everything is so much effort. Say values to sum are in
A2:A6. In A7, use
=SUM(A2:OFFSET(A7,-1,0))
, the formula will adjust if you add a row between 6 and 7.

Rgds,
Andy
 
Back
Top