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

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

# 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
• 2
1 Solution

Commented:
Try this - it works for me.

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

Dave
0

Commented:
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

Commented:
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

Author Commented:
Thanks again, Barry.

I like the ISNumber function...

Gary
0

## Featured Post

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