Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to exclude values that appear as #value

    I am currently using =SUMIF('Raw Data TNL EXPORT'!$B$2:$B$123,Sheet1!$A$2,'Raw Data TNL EXPORT'!$C$2:$C$123) and am having a tough time trying to figure out how to exclude values that appear as #value.


    When I encounter a record where value is #value, it does not calculate. In the case below, Sarah's value shold be 94%, excluding the #value.
    On worksheet Titled Raw Data TNL Export I have the following:
    Column B Column C
    Janet 100%
    Janet 80%
    Sarah 94%
    Sarah #Value

    On worksheet Titled sheet 1 I have the following:
    Column A Column B
    Janet 180%
    Sarah #Value
    Attached Images Attached Images

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Try this ARRAY formula (CTRL+Shift+Enter):

    =SUMPRODUCT((RawData!$B$2:$B$123=Sheet1!$A$2)*(IF( ISERROR(RawData!$C$2:$C$123),0,$C$2:$C$123)))
    Last edited by kweaver; 2014-10-31 at 11:07.

  3. #3
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help

    Quote Originally Posted by kweaver View Post
    Try this ARRAY formula (CTRL+Shift+Enter):

    =SUMPRODUCT((RawData!$B$2:$B$123=Sheet1!$A$2)*(IF( ISERROR(RawData!$C$2:$C$123),0,$C$2:$C$123)))

    I dont get error but it makes them zeros. I have attached a very small sample and just reduced it to a small data set so you can see what I am trying to do:

    Formula Help.jpg

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    It's difficult to tell from this images. Could you attach a sample Excel workbook?

    Did you enter my formula as an array formula? If not, you'll surely get a 0 as a result.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    FM,

    If you're willing to add a column:
    B2: =SUMIFS(Observations,Groups,A8,TestError,FALSE)
    Errtest.JPG
    I assume the above is the result you want?

    Test File: FreeMance.xlsx

    Note: Column C has the formula: C11 =ISERROR(B11)
    Then filled down.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by freemance View Post
    i dont get error but it makes them zeros. I have attached a very small sample and just reduced it to a small data set so you can see what i am trying to do:

    Formula Help.jpg
    i have attached the sheet. I had remove personal info. I did hit shft+ctrl+enter
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    FM,

    Here's my solution w/your data. Is this what you're after?
    FreeMance2.JPG

    Test File: FreeMance2.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes...that was perfect...and such a small formula. I need to study it a bit to understand it. Thank you again and have blessed weekend rg

Posting Permissions

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