I have a excel spreadsheet, and while I like to think of myself as quite the excel wizard, this one has me stumped, it's probably something ridiculous but I really can't get it too work. I have a table of two columns, and a cell containing a specific number, for example, in A1 there is an amount of 10. The B column would be filled with number, so say B1 contains 1 and B2 contains 2, and the same again for the C column.

Now, I would like the cell D1 to contain the total for referencing A1, subtracting all the amounts from the B column, and then adding all of the amounts from the C column. Now, the formula I have used is as follows:

``````=A1-(B1:B10)+(C1:C10)
``````

Now i'm not sure if this is causing the dreaded #VALUE error because their are empty cells in the formula, while the B or C column may not be full right now, they could be full another time which is why the formula has to stretch so far the column so I don't have to alter the equation everyday.

I've tried formatting the cell and various other things I could think of but it just doesn't seem to work, i'd be extremely appreciative to anyone that can help me out with this one, this little spreadsheet is meant to make my life easier but instead it's just ruining my happy little mood.

Thanks again guys!

-

migrated from stackoverflow.comJul 8 '13 at 17:05

This question came from our site for professional and enthusiast programmers.

`B1:B10` is an array (you can think of them like matrices) and there's no operation on it in your formula. What you probably want to do is:

``````=A1-SUM(B1:B10)+SUM(C1:C10)
``````
-
Why I didn't think of that myself I will never know, that's ridiculous, naturally it worked, and thank you very much! – Aaron Lee Jul 8 '13 at 9:08
@AaronLee You're welcome :) – Jerry Jul 8 '13 at 9:09
Stick around for a while if you can, I may have a few more questions coming soon because this has got to get a whole lot more information and formulas in there soon haha – Aaron Lee Jul 8 '13 at 9:10
Ok man, thanks for the advice! – Aaron Lee Jul 8 '13 at 9:12

You will need something like this :

``````=A1-SUM(B1:B10)+SUM(C1:C10)
``````
-
Thanks for the answer, but Jerry got their first, upvote for both still, thanks guys. – Aaron Lee Jul 8 '13 at 9:09