Results 1 to 5 of 5

Thread: Nested COUNTIF?

  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Unanswered: Nested COUNTIF?

    Hey all,

    It's MillB - and I'm stumped once again.
    I have literally wrapped around in circles trying to find this solution.
    Can one of you function pros give me a hand?

    Here's what I got.
    Screenshot 1 - in cell H19 is suppose to be the results of my calculation.
    Screenshot 2 - The column I'm trying to calculate.

    What I need this to do is count only the columns that have records, and within those only the ones <3. Make sense?

    Once I have this it should be easy to replicate for <7 days and <10 days.

    Much Thanks!
    Attached Thumbnails Attached Thumbnails Screenshot1.PNG   Screenshot2.PNG  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    A little confused but would this do it

    =SUMPRODUCT((B1:B65535>3)*1)


    ??


    MTB

    PS for some bizarre reason the above also counts text entries so I submit this in mitigation !!

    =SUMPRODUCT((B1:B65535>3)*ISNUMBER(B1:B65535)*1)
    Last edited by MikeTheBike; 05-29-08 at 09:15.

  3. #3
    Join Date
    Dec 2006
    Posts
    39

    Hey Mike

    No, that doesn't do it.
    The correct amount is currently in the cell H19 in screenshot 1 (81).

    This calculation needs to only find the <3 records from B2 to the last record.
    However, there's a catch. These records get updated via ODBC from Access, so, the length of the list will change each time it's updated.

    Does this make more sense of what I'm trying to accomplish?

    Thanks,

    MillB

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    You should probably set up a dynamic named range, and use that in your formulas, so that it will always refer to the correct range.

    MyRng

    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)

    If B1 is filled then use this:

    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi
    Does this make more sense of what I'm trying to accomplish?
    Not really.

    If you are trying to COUNT the number of cells in Sheet1, Column B, Row 2 and down, that have a value greater than 3 (?), then this seems to do that

    =SUMPRODUCT((Sheet1!B2:B65536>3)*ISNUMBER(Sheet1!B 2:B65536))

    If that is not what you want, then perhaps a little more info is required!

    However, if that is what you want, and it doesn’t do what I think, perhaps you could indicate what it does do?


    MTB

Posting Permissions

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