SUM formula but with some cells that contain N/A

[helpme1992]

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

 

[Jonmo1]

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 ??

 

[Jonmo1]

Which version of Excel are you using?

XL2010+ you could use Aggregate

=AGGREGATE(9,6,Tracking!H10,Tracking!K10,etc)

 

[helpme1992]

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

 

[steve the fish]

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))

 

[Jonmo1]

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.

 

[Marcílio_Lobão]

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.

 

[Jonmo1]

@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.

 

[helpme1992]

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

 

[Jonmo1]

please could you give me a little help on how to apply this to aforementioned below formula

You need only add the rest of the cell references to the formula

=AGGREGATE(9,6,Tracking!H10,Tracking!K10,etc)