Bypassing #N/A's and using SUM's

[cakeytaste]

Good afternoon, all. I've learned a whole lot by lurking on these forums for quite some time. Hopefully I can get a bit of help with an issue I can't seem to resolve.

I'm attempting to SUM a column of data (5 cells). It's possible that I will have #N/A in any number of them, as they call their data from another cell. Specifically, if C16 is:

=INDEX(plan_minutes,MATCH(B16,plan_name,0))

Then it checks for the text in B16 (which is populated via a dropdown list), matching the index to minutes billable (plan_minutes) and returns the value that corresponds. For example, B16 is "Monthly 50" and includes 50 billable minutes, C16 returns "50" as its result.

This becomes an issue when a billing plan isn't selected in B16, and returns #N/A for C16. For formatting and cleanliness, I have conditional formatting on C16 that states =ISNA(C16) and changes the font color to white.

WE'RE STILL NOT TO MY PROBLEM!

I can deal with the #N/A in C16-C20 with conditional formatting. What I can't deal with is my billable minutes total cell (E50).

The cell should add the billable minutes in C16-C20, but doesn't. I believe it's because the information in C16-C20 is actually text, returned by the index, and not a true number. Is there a way around this or should I change my method in some way?

I've tried everything I can think of in E50.

 

[Aladin Akyurek]

You're 100% on, jonmo1.

I believe I found my error. My "Name Manager" was putting out this:

={"450","900","1,350","100,000","125,000","150,000","0","0"}

Removing the quotation marks appears to have corrected this. Should I have further errors in this worksheet, I'll update the thread.

Thanks so much to everyone for the help.

Now you have discovered the reason for the text numbers in your range and corrected for that, you can either keep #N/A's and run:

=SUM(SUMIF(C16:C20,{"<0",">0"}))

or eliminate them (costly) and run a simple SUM().