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

C

cakeytaste

New Member
Joined
Jan 20, 2010
Messages
5
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:
Code: 
=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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
One of...

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

if C16:C20 houses true numbers, probably intermixed with #N/A's.

Otherwise...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(C16:C20)+0),C16:C20))
 
Upvote 0
I get an error that focuses on the "+0" when using the second formula.

The first formula is similar to what I've been trying. It isn't returning a #N/A in E50, but the cell stays completely blank. I attempted your recommendation and several similar formulas, for example:
Code: 
=SUMIF(C16:C20,"<>#N/A",C16:C20)
I'm confused.
 
Last edited:
Upvote 0
cakeytaste said:
I get an error that focuses on the "+0" when using the second formula.

The first formula is similar to what I've been trying. It isn't returning a #N/A in E50, but the cell stays completely blank. I attempted your recommendation and several similar formulas, for example:
Code: 
=SUMIF(C16:C20,"<>#N/A",C16:C20)
I'm confused.
Click to expand...

They shoud be...

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

=SUM(IF(ISNUMBER(C16:C20+0),C16:C20))

The second needs control+shift+enter.
 
Upvote 0
I would eliminate the #N/A error in the first place, rather than Hide it with conditional formatting...

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

to

=IF(ISNUMBER(MATCH(B16,plan_name,0)),INDEX(plan_minutes,MATCH(B16,plan_name,0)),"")

Now that formula returns "" instead of #N/A
And sum formulas will ignore ""
 
Upvote 0
Jonmo1: Thanks so much for that, I've gone and removed all conditional formatting and replaced the INDEX with your formula. The C16:C20 still pull correctly, and without data in B20, C20 presents "". That is perfect.

HOWEVER, I still get no data in E50, even when using a basic =SUM(C16:C20). Could this be a different problem that I'm seeing?
 
Upvote 0
If by "I still get no data" you mean, the sum returns 0, then I would guess your data in plan_minutes are not really numbers, but "numbers stored as text"

Test with this

=ISNUMBER(A1) where A1 is a number in plan_minutes
does that give true or false?
 
Upvote 0
cakeytaste said:
Aladin Akyurek: Using both of your formulas returns a blank E50 cell. Thoughts?
Click to expand...

My fault... The second formula is what you need:

=SUM(IF(ISNUMBER(C16:C20+0),C16:C20+0))

Needs control+shift+enter. The +0 bit is needed in order to convert the text numbers in the range into true numbers.

Eliminating #N/A does not suffice here...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
IF/MATCH statement that includes the SUM
Replies
5
Views
320
Fluff
Fluff
VinceF
  • Question Question
VBA To Copy & Paste Value
Replies
0
Views
366
VinceF
VinceF
D
  • Question Question
Scoring weighted permutations
Replies
1
Views
224
StephenCrump
S
C
  • Solved
Highlight entire rows in used range where any cell contains searched text
Replies
5
Views
583
CorrieAnn
C
W
  • Question Question
Adding another condition to IF(AND) formula
Replies
6
Views
275
etaf
etaf

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top