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
garyrobbinsAsked:
Who is Participating?
 
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
 
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
 
garyrobbinsAuthor Commented:
Thanks again, Barry.

I like the ISNumber function...

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.