+ Reply to Thread
Results 1 to 20 of 20

Countif, do not include greater than a set value

  1. 03-16-2013, 04:07 AM #1
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Countif, do not include greater than a set value

    I have a column checking a dynamic range using the below
    In R6 I have

    Please Login or Register  to view this content.
    then in the rows R7 and down I have this array pasted into them

    Please Login or Register  to view this content.
    It gives me all the unique numbers the column AD

    I dont want it to include any number great than 139999

    what do I need to change in the above to have it work the way I would like?

    Any help appreciated

    bungslea
    Reply With QuoteRegister To Reply
  2. 03-23-2013, 08:17 PM #2
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Bump
    Reply With QuoteRegister To Reply
  3. 03-23-2013, 08:23 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    Not sure if this will work or not, give it a shot...

    {=IF(and(COUNTIF(AD$4:AD$1000,">"&R6,max(AD$4:AD$1000,"<"&140000),INDEX(AD$4:AD$1000,MATCH(COUNTIF(AD$4:AD$1000,"<="&R6),COUNTIF(AD$4:AD$1000,"<"&AD$4:AD$1000),0)),"")}
    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
    Reply With QuoteRegister To Reply
  4. 03-23-2013, 11:16 PM #4
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Bugga,
    To many arguments for this function. With the countif being the problem
    Reply With QuoteRegister To Reply
  5. 03-23-2013, 11:46 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    go through the formula with the formula editor and see if you can see where the error lies?
    Reply With QuoteRegister To Reply
  6. 03-23-2013, 11:57 PM #6
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    error.png

    Best way to give it to you ??
    Reply With QuoteRegister To Reply
  7. 03-23-2013, 11:59 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    ok try it like this instead...
    "<140000"
    Reply With QuoteRegister To Reply
  8. 03-24-2013, 12:06 AM #8
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Nope,
    still gives me the same error at the same place
    Reply With QuoteRegister To Reply
  9. 03-24-2013, 12:13 AM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    Max(ad$4:ad$1000)<140000)
    Reply With QuoteRegister To Reply
  10. 03-24-2013, 12:23 AM #10
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    error2.png

    To many arguements again, but now in the COUNTIF ??
    Reply With QuoteRegister To Reply
  11. 03-24-2013, 12:28 AM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    {=IF(and(COUNTIF(AD$4:AD$1000,">"&R6),max(AD$4:AD$1000)<140000),INDEX(AD$4:AD$1000,MATCH(COUNTIF(AD$4:AD$1000,"<="&R6),COUNTIF(AD$4:AD$1000,"<"&AD$4:AD$1000),0)),"")}

    Its a bit hard for me to test the formula, but you need to make sure that the parentheses close in the correct places. Work from the inside out and make sure they are matched and closed...
    and( COUNTIF(AD$4:AD$1000,">"&R6) , max(AD$4:AD$1000) <140000), INDEX(AD$4:AD$1000,MATCH(COUNTIF(AD$4:AD$1000,"<="&R6),COUNTIF(AD$4:AD$1000,"<"&AD$4:AD$1000),0))
    Reply With QuoteRegister To Reply
  12. 03-24-2013, 12:38 AM #12
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Ok, the formula is now accepted.

    But it only works as far as row 6, the first bit of code. The piece we have been playing with is in row 7 and down, they are not populating, remaining blank
    Reply With QuoteRegister To Reply
  13. 03-24-2013, 12:45 AM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    then use the same approach for the 1st formula...

    =IF(and(AD4:AD6<1,max(AD$4:AD$1000) <140000),"",MIN(AD$4:AD$1000))
    Reply With QuoteRegister To Reply
  14. 03-24-2013, 12:54 AM #14
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Book3.xlsb

    There you go, sorry about file size, best I could get it down to for some reason
    Reply With QuoteRegister To Reply
  15. 03-24-2013, 01:03 AM #15
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    nope, does not work either

    Sorry for buggering you around
    Reply With QuoteRegister To Reply
  16. 03-24-2013, 07:15 AM #16
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Countif, do not include greater than a set value

    These kind of formulas are slightly too complicated for me so I had to resort to google.
    http://www.get-digital-help.com/2009...remove-blanks/

    A small modification and I had something that seems to work.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  17. 03-24-2013, 06:30 PM #17
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,962

    Re: Countif, do not include greater than a set value

    Thanks for the file

    OK after seeing what you are trying to do, I have changed things a bit. I added a helper column after AD (I used AE, you can use what you want - you can hide it if you want). I used this inthe helper column, copied down...
    =IF(AD4>139999,"",AD4)

    Then in R6, copied down, I used this...

    =INDEX($AD$4:$AD$1000,MATCH(0,INDEX(COUNTIF($R$5:R5,$AD$4:$AD$1000),0,0),0))

    let me know if you can work with this?
    Reply With QuoteRegister To Reply
  18. 03-28-2013, 03:16 AM #18
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Sorry for the delay. Life in the real world caught up with me

    FDibbins, I used AB as the helper column, I changed the formula to show that 

    Please Login or Register  to view this content.
    And in the helper column

    Please Login or Register  to view this content.
    works most of the time, then every once in a while it gives me a blank row. It seems to be when there is an empty space in the AE column, as in the AE12 in the book3 file I posted.

    Jacc, yours works great, any chance of sorting the results AtoZ ? i tried with the filter, but it bounced back to the original layout as soon as i hit the button.
    If not it does not mater.

    Thanks to both of you guys
    Reply With QuoteRegister To Reply
  19. 03-30-2013, 01:28 AM #19
    bungslea
    bungslea is offline
    Registered User
    Join Date
    01-31-2013
    Location
    Brisbane, Australia. high and dry
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Countif, do not include greater than a set value

    Thanks guys
    Last edited by bungslea; 03-31-2013 at 04:17 AM. Reason: Start new thread
    Reply With QuoteRegister To Reply
  20. 04-01-2013, 04:00 AM #20
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Countif, do not include greater than a set value

    Sorry for late reply, I frequently miss posts here and there for some reason.

    Anyway, here's a simple solution to the sorting problem, just used a helper column.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1