Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria Assistance (03)

    I am trying to extract data from a range using advanced filters to another location. My challenge is setting the criteria. The range includes 14 columns with a heading for "Accounts", "Product","Jan","Feb","Mar" and etc.. There are some accounts that have a zero amount for Jan through Dec while some may only contain an amount in one month.

    I only want to extract those accounts that have an amount in any of the twelve months or in other words exclude those accounts where the amount is zero for Jan through Dec.

    Your assistance is appreciated,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria Assistance (03)

    You could add another column that would be a count of the non-zero amounts and filter on that column.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria Assistance (03)

    That makes sense but unfortunately I can not add a column. I'm locked in to the 14 columns.

    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Criteria Assistance (03)

    Set the criteria value to be (change range as desired, and do not have a header for it):
    =Sum(c2:n2)>0

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria Assistance (03)

    Steve,

    I can not get it to work. I have provided a sample file.

    Thanks,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria Assistance (03)

    The formula in the criteria range should be

    =SUM(Source!C2:O2)>0

    instead of

    =SUM("C2:02")

    1) you forgot the sheet
    2) there should be no quotes around the cell reference
    3) you had 02 (zero two) instead of O2 (oh two)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria Assistance (03)

    You can say that again. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> It's back to basics.

    Regards,
    John

Posting Permissions

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