SUM formula but with some cells that contain N/A

helpme1992

New Member
Joined
Sep 2, 2016
Messages
13
Hi all,

In a MASSIVE predicament and any help I can glean would be amaaaaaaazing!

I am trying to do a simple SUM formula but with some cells that contain N/A and I REALLY need to find a way of the formula ignoring the N/A values / adding this as 0.

I am not doing a simple '=SUM(A1:A10)' I am doing a 'SUM(Tracking!A1,Tracking!D1,Tracking!G1) etc.

I have tried the formula '=SUMIF(" ", ">=0") + SUMIF(" ", "<0") but due to the length of the formula (ei the number of cells being added)I am told there are too many arguments in the formula and it does not work.

My formula currently looks like this:

=SUM(Tracking!H10,Tracking!K10,Tracking!N10,Tracking!Q10,Tracking!T10,Tracking!W10,Tracking!Z10,Tracking!AC10,Tracking!AF10,Tracking!AI10,Tracking!AL10,Tracking!AO10,Tracking!AR10,Tracking!AU10,Tracking!AX10,Tracking!BA10,Tracking!BD10,Tracking!BG10,Tracking!BJ10,Tracking!BM10,Tracking!BP10,Tracking!BS10,Tracking!BV10,Tracking!BY10,Tracking!CB10,Tracking!CE10,Tracking!CH10,Tracking!CK10,Tracking!CN10,Tracking!CQ10,Tracking!CT10,Tracking!CW10,Tracking!CZ10,Tracking!DC10,Tracking!DF10,Tracking!DI10,Tracking!DL10,Tracking!DO10,Tracking!DR10,Tracking!DU10,Tracking!DX10,Tracking!EA10,Tracking!ED10,Tracking!EG10,Tracking!EJ10,Tracking!EM10,Tracking!EP10,Tracking!ES10,Tracking!EV10,Tracking!EY10,Tracking!FB10)

How can I make the above formula ignore N/A values / make these 0?

THANK YOU in advance,

Mx
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
The easiest solution would be to eliminate the #N/A errors at their source.
What is the formula on the Tracking sheet that returns those errors?
You could add IFERROR to that formula to make them return 0 instead of #N/A

It also appears you have a pattern, summing every 3rd cell (sum A, skip B and C, Sum D etc..)
Is there anything in another row that can be used as a key to identify which cells should be summed ?
So we can say something like
Sum all cells in row 10 if the value in row 9 is "X" or whatever ??
 
Upvote 0
Which version of Excel are you using?

XL2010+ you could use Aggregate

=AGGREGATE(9,6,Tracking!H10,Tracking!K10,etc)
 
Upvote 0
Hi MrExcel (MVP!)

Thank you for your support!

I am using Excel 2010 and the formula that returns the N/A is

=INDEX('Resources - Days - Inception'!$E$8:$E$38,MATCH(Tracking!C10&Tracking!D10,'Resources - Days - Inception'!$C$8:$C$38&'Resources - Days - Inception'!$D$8:$D$38,0))*VLOOKUP(C10,'Rate Table'!$B$4:$C$34,2,FALSE)

I cannot amend the formula / remove it because I am making a template workbook. Sometimes not every cell will be filled with data in Sheet 1(for example) but every cell within my table within Sheet2 (for example) must have the formula to accommodate a user imputing data into all the cells in Sheet 1.

Thank you sooooo much!

Mx
 
Upvote 0
I was trying with this, if it is, as looks like every 3 columns.

=SUMPRODUCT(--(MOD(COLUMN(Tracking!F1:FB1)-COLUMN(Tracking!F1)+1,3)=0),IFERROR(Tracking!F1:FB1,0))
 
Upvote 0
I cannot amend the formula / remove it because I am making a template workbook.
Why does that mean you can't ammend the formula?

If you really can't change that formula, then use the Aggregate I suggested.


Otherwise, I would suggest ammending that formula as below.
=IFERROR(INDEX('Resources - Days - Inception'!$E$8:$E$38,MATCH(Tracking!C10&Tracking!D10,'Resources - Days - Inception'!$C$8:$C$38&'Resources - Days - Inception'!$D$8:$D$38,0))*VLOOKUP(C10,'Rate Table'!$B$4:$C$34,2,FALSE),"")

Then your original SUM formula will work just fine.
 
Upvote 0
helpme1992, Good morning.

Dear Jonmo1, Good morning.

Let me give you my 2 cents of help.

Why not to create a Named Range and use a SUMIF Function?

Suppose:

Your group of cells will be named: AMBIENT

Then your formula can be:
=SUMIF(AMBIENT, ">0")


Is it possible for you?

Please, tell us if it worked for you.
I hope it helps.

 
Last edited:
Upvote 0
@Marcilio
Sumif can't use a range of Non-Contiguous cells (even if it's in a named range).
You'll get a #Value! error.
 
Upvote 0
Thank you for your help @Marcilio

StevetheFish please can you elaborate a little?

MrExcel(MVP!) thank you for both options, the reason I say I cannot change the formula is simply because it would result in hours of work as there are hundreds of cells that would each need manually changing.

The second option of not amending the formula and using the AGGREGATE would be amazing - please could you give me a little help on how to apply this to aforementioned below formula;

=SUM(Tracking!H10,Tracking!K10,Tracking!N10,Tracking!Q10,Tracking!T10,Tracking!W10,Tracking!Z10,Tracking!AC10,Tracking!A F10,Tracking!AI10,Tracking!AL10,Tracking!AO10,Tracking!AR10,Tracking!AU10,Tracking!AX10,Tracking!BA10,Tracking!BD10,Trac king!BG10,Tracking!BJ10,Tracking!BM10,Tracking!BP10,Tracking!BS10,Tracking!BV10,Tracking!BY10,Tracking!CB10,Tracking!CE1 0,Tracking!CH10,Tracking!CK10,Tracking!CN10,Tracking!CQ10,Tracking!CT10,Tracking!CW10,Tracking!CZ10,Tracking!DC10,Tracki ng!DF10,Tracking!DI10,Tracking!DL10,Tracking!DO10,Tracking!DR10,Tracking!DU10,Tracking!DX10,Tracking!EA10,Tracking!ED10, Tracking!EG10,Tracking!EJ10,Tracking!EM10,Tracking!EP10,Tracking!ES10,Tracking!EV10,Tracking!EY10,Tracking!FB10)

Thank you for your on going help!

Mx
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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