[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

How do I modify my SumProduct formula where #N/A errors are included?

Hello again,

My data set actually includes some #N/A errors.  How do I modify my formula to adjust for #N/As present?

=SUMPRODUCT((MySumRange_Dept)
Where MySumRange_Dept is:
=THL!$M$6:INDEX(THL!$M:$M,MATCH(rept("z",20),THL!$M:$M))
(The data is text only.)

Thanks, Gary
0
garyrobbins
Asked:
garyrobbins
  • 2
1 Solution
 
dlmilleCommented:
Try this - it works for me.

{=SUMPRODUCT(IF(ISERROR(mySumRange),0,mySumRange))} - CTRL-SHIFT-ENTER to confirm

Dave
0
 
barry houdiniCommented:
What are you trying to do exactly? The formula

=SUMPRODUCT(MySumRange_Dept)

...won't do anything except sum values in the range specified, so SUMPRODUCT is superfluous. I suspect there is more complexity involved - how you handle errors is different depending on what the formula is supposed to do

If you do just want to sum the values in that range then try using SUMIF

=SUMIF(MySumRange_Dept,">-9.99E+307")

That will ignore #N/A errors (or any other errors)

regards, barry
0
 
barry houdiniCommented:
OK I didn't see this part

The data is text only

If you have a SUMPRODUCT formula of the form

=SUMPRODUCT((A2:A10="x")*(B2:B10="y"),C2:C10)

then that sums col C when A is "x" and B is "y"....but if there might be #N/A errors in A2:A10 only then a way round that is like this

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,"x",0))*(B2:B10="y"),C2:C10)

does that help?

barry
0
 
garyrobbinsAuthor Commented:
Thanks again, Barry.

I like the ISNumber function...

Gary
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now