Hi!
I have some pre-formatted data. At the bottom of each set is a sum total. How can I make it ignore when the data is returning an error? I tried SUMIFS(A2:A16,A2:A16,"TYPE()<>16"), but that didn't work. I'm not really sure how I can get around this, as the sum total always returns an error if any of the data types return as an error! Please help!
I think the best solution is to prevent the Excel errors, in the first place. If you want help with that, provide an example Excel file and explain the situations that cause the Excel errors.Originally Posted by AstToTheRegionalMGR
Otherwise, note that SUMIFS and the TYPE function cannot be used in that way. Instead, array-enter the following formula (press ctrl+shift+Enter instead of just Enter):
=SUM(IF(ISERROR(A2:A16)=FALSE,A2:A16))
Last edited by joeu2004; 03-25-2015 at 10:40 AM. Reason: cosmetic
I have hundreds of these data sets, and error-preventing in the first place will be the bigger headache, unfortunately. I thought this might come to array formulas, which I just don't understand. I'll use yours and see if it works, though. Thanks!
Another Array Formula which you can try in this situation......
Confirmed with Ctrl+Shift+Enter instead of Enter alone.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
a b a b #ERROR a
A6=SUMIFS(A1:A5,B1:B5,"a")
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
Another alternative:
=SUMIFS(A1:A16,A1:A16,"<>#N/A",A1:A16,"<>#DIV/0!")
In other words, exclude specific Excel errors. See the ERROR.TYPE help page for a list of all Excel errors, if you wish to exclude them all.
joeu2004,
I didn't think you could use "#N/A" as a reference to an error. Am I wrong about that? I thought Excel recognized that as text when you typed it into a formula?
I'm only receiving #N/A errors, so that could definitely work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks