Ignore N/A within a complex formula.

Hello everybody!

I am calculating percentages of "Yes" (success rate) with a formula that ignores hidden rows. However, I want also to ignore N/A but I don't know how to combine this. Here is the formula that I'm using to ignore hidden cells and calculate percentages of "Yes":

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Raw Data'!L3:L300,ROW('Raw Data'!L3:L300)-ROW('Raw Data'!L3),0,1)),--('Raw Data'!L3:L300="Yes"))/SUBTOTAL(103,'Raw Data'!L3:L300)

Thank you in advance!

If, conversely, you're saying your L3:L500 values are result of formulas, and can return #N/A (which you want to keep but ignore in your calc) then:

Formula:

Please Login or Register  to view this content.

Hi and thank you for your reply.

I have already tried by adding IFERROR in the beginning and "") at the end, but it is not working. The N/A is not an actual error like #N/A. It's just a 3rd option e.g. "Yes", "No", "N/A"

Hi, if the n/a is a literal string your existing calculation would work... unless you're saying you want to adjust the denominator such that it excludes the n/a string?

Formula:

Please Login or Register  to view this content.

if that's not what you mean I'd suggest posting a sample file to better illustrate

I cannot 100% follow either.

If you column L range contains "yes"/"no"/"n/a", I don't see why you would need to ignore "n/a" as your sumproduct already only considers "yes" values?

Perhaps the problem is rather in the denominator of your formula, i.e. the last subtotal part that needs to be amended in a similar way to ignore hidden values?

Edit: I guess what XLent says above is where I was heading, too.

Brilliant! It worked!

Thank you so much XLent, and of course everybody!!