# Thread: how to exclude values that appear as #value

1. ## 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

2. 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)))

3. ## Formula Help

Originally Posted by kweaver
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. 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. 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

6. Originally Posted by freemance
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

7. FM,

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

Test File: FreeMance2.xlsx

HTH

8. 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
•