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

 

[helpme1992]

Hi MrExcel MVP,

Thank you this has worked perfectly (feel like an idiot for asking that question!)

Is there anyway of dragging this across to the next column? I can drag down and this works but not across.

I have pasted this into cell E39 - perfect.
In cell F39 I need to do exactly the same thing / formula but remove the first entry of the formula ei - Tracking!H10.
Then cell G39 is the same but this time minus the first entry from cell F39 - Tracking!K10
And so on and so on.

Is there a quicker way of doing this rather than manually deleting the last cells formulas first entry every time?

 

[Jonmo1]

You're welcome.

No, I can't think of any way to do that.

I'd still be interested in an answer to this question
A simpler solution may be possible if there is any type of pattern in another row that identifies the cells to sum.

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

 

[helpme1992]

Thank you again MrExcel I appreciate it SO much!

You really are the MVP