+ Reply to Thread
Results 1 to 7 of 7

Using COUNTIF with a dynamic range

  1. #1
    Registered User
    Join Date
    04-03-2013
    Location
    london
    MS-Off Ver
    Excel 2008
    Posts
    5

    Using COUNTIF with a dynamic range

    I am a bit of a newbie to excel. I am trying to use the Countif function, but where I am specifying the range, I want to start in a fixed cell, for example A2, and continue to count in column A until a number, X, which is calculated by adding up the values in other cells, so it will change.
    Is there a way to do this? Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Using COUNTIF with a dynamic range

    Hi and welcome to the forum

    Im having a hard time visualising what you are trying to do, but maybe take a look at using a helper column to add the values, and then use countifS() to include that halper column?
    If that doesnt work for you, I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-03-2013
    Location
    london
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Using COUNTIF with a dynamic range

    I don't think I explained it very well. So, I have some data that I am exporting from another program into excel (its survey responses). I have 5 columns, and 300 rows. Each row can have text in none, one, two, three, four or all five columns. I want to count the non-blank cells in each column BUT I want to use countifs, so that if one cell in one row is populated, i want to check if the other 4 cells in that row are populated or not. If they are not populated, ie there is only 1 cell populated in a certain row, then I want to record this, these are the ones I want to count. Does this make sense?! I am trying to use this: =countifs(A1:A300,"Something",B1:B300,"",C1:C300,"",D1:D300,"",E1:E300,""). I am hoping this will count the number of non-bank cells in column A where the corresponding cells in columns B-E are ALL blank. But it doesn't work! Any ideas?!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Using COUNTIF with a dynamic range

    Yep; i agree with FDibbins,

    Why not just post an excel file, without confidentional information.

    Also add the desired (expected) result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using COUNTIF with a dynamic range

    try
    =countifs(A1:A300,"<>",B1:B300,"=",C1:C300,"=",D1:D300,"=",E1:E300,"=")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    04-03-2013
    Location
    london
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Using COUNTIF with a dynamic range

    Thanks. That doesn't work! I should have mentioned I am using Excel 2008 on a mac, does this make a difference?

    Thank you!

  7. #7
    Registered User
    Join Date
    04-03-2013
    Location
    london
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Using COUNTIF with a dynamic range

    Oh, I"ve done something now and it works! Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1