Countif with indirect formulas needed

Discussion in 'Microsoft Excel New Users' started by Matt05D, Jul 16, 2012.

  1. Matt05D

    Matt05D

    Joined:
    Jul 16, 2012
    Messages:
    1
    Likes Received:
    0
    Hi there,

    I am working on a spreadsheet in excel 2007 involving a number of copy and paste macros. They move data from one sheet onto another sheet by shifting all existing data (on the sheet the data is moving to) down one row, and then pasting the new values in. This is working well but I want to put in a simple countif formula, like so:

    Code:
    =COUNTIF(B2:B367,">0")
    However, when the new data shits in, the B2 becomes B3 and the B367 becomes B368 and then this carries on so it is only ever counting the first piece of data that was entered.

    How can I rectify this, I have read about indirect formulas, however I cannot get these to work, and using $B$2 doesn't work either, although you're probably just thinking why would i even bother trying that anyway!!

    I have a very similar problem with cumulative addition formulas in cells (the answer may be very similar). I wish to add 2 cells, (not next to each other) the values of which will change since the data is shifting down one space each time with a copy and paste macro? could anyone supply a formula for that too please.

    Many thanks,
    Matt

     
    Matt05D, Jul 16, 2012
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Randy

    Formulas needed for Calendar?

    Randy, Jul 6, 2005, in forum: Microsoft Excel New Users
    Replies:
    4
    Views:
    271
    Randy
    Jul 8, 2005
  2. busterbrown885

    Help needed on formulas

    busterbrown885, Aug 6, 2005, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    209
    Guest
    Aug 6, 2005
  3. LittleAths Volunteer

    Help needed with formulas

    LittleAths Volunteer, Oct 17, 2008, in forum: Microsoft Excel New Users
    Replies:
    9
    Views:
    171
    LittleAths Volunteer
    Oct 19, 2008
  4. djc

    Conditional format using "Indirect"

    djc, May 5, 2009, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    214
    T. Valko
    May 14, 2009
  5. TechieGirl

    Re-post:Formula Finesse Needed: COUNTIF or DCOUNT or Something bet

    TechieGirl, Sep 30, 2009, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    337
    TechieGirl
    Sep 30, 2009
Loading...

Share This Page