Sumproduct that filters out errors vlaues
I know how to create sumproduct formulas that have conditions (see below). But is there a way to create a sumproduct formula that only sums values that aren't errors?
Here is my formula that accounts for 2 filters ("Americas" and D$6):
Formula:
Please Login or Register to view this content.
My data includes "#DIV/0!" errors in column T:T and I want to avoid those rows. Please tell me if I need to clarify.
Thanks
Last edited by Craig K.; 05-21-2013 at 03:52 PM. Reason: resolved the issue that necessitated the thread
I'm afraid sumproduct can't do that.
Much better off adjusting the formulas in Column T to return a 0 instead of Div/0 errors.
Or use an array formula enterd with CTRL + SHIFT + ENTER
=SUM(IF(('5-8-13'!$E$2:$E$5000="Americas")*('5-8-13'!$H$2:$H$5000=Americas!D$6)*ISNUMBER('5-8-13'!$T$2:$T$5000),'5-8-13'!$T$2:$T$5000))
This arrayed function would also work
=SUMPRODUCT(--($E$2:$E$5000="Americas")*($H$2:$H$5000=D$6)*IF(ISERROR($T$2:$T$5000),0,$T$2:$T$5000))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Jonmo, the problem is that the values are coming from a SharePoint list download and I'm trying to avoid entering in arrayed functions.
I had a hunch this would be the answer. Still had to aks though.
Thanks.
Then I'll go back to this pointOriginally Posted by Craig K.
What is the formula in column T ?
You can probably just add IFERROR to it
=IFERROR(originalformulaincolumnT,0)
You can use the SUMIFS function:
=SUMIFS('5-8-13'!$T$2:$T$5000,'5-8-13'!$T$2:$T$5000,"<1E100",'5-8-13'!$E$2:$E$5000,"Americas",'5-8-13'!$H$2:$H$5000,Americas!D$6)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Problem was resolved by fixing the equations inside the SharePoint list. I also switched to using SUMIFS instead of SUMPRODUCT.
marking this thread [solved] to close it out.
Glad to help, thanks for the feedback.
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks