Copying formula

  • Thread starter Thread starter Denny Leung
  • Start date Start date
D

Denny Leung

Dear all,

I've a problem of copying formula. In cell G1, I set a
formula =COUNTA(A2:A10) to count the things in cells A2 to
A10. In G2, I want to count things in rows of Column B
same as Column A. When I drag the formula from G1 to G2,
the formula in G2 changed to =COUNTA(A3:A11) instead of
=COUNTA(B2:B10). Any solution can help because I want to
do same thing to Column C and so on.

Thanks in advance.
 
Hello Denny

Here's one way:

In G1 enter

=COUNTA(OFFSET($A$2:$A$10,0,ROW()-ROW($G$1)))

Drag down.

If you start in another row e.g. L2 change
ROW($G$1) in the formula to ROW($L$2)
(or ROW($G$2))
This must be done, because ROW()-ROW($G$1)
acts as a counter (0,1,2,3,4, etc)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
It's sort-a like being on the dance-floor; your the guy and excel is the
gal; Excel follows YOUR lead... In your case here You have "moved
(dragged) -downward"
G1 to G2 - so Excel modifys your original formula "Downwards" beginning with
from A2 to A3; What you want to do since you want Excel to read and bring
back the B:B column now in addition to the A:A column you should click on G1
and drag "Right". Do so and see if it doesn't respond to your "lead". HTH
(Happy dancing)..
 
Back
Top