Formula for subtracting cell ranges

[kstruckinman]

Finally I have figured out how to subtract a range of cells to another range of cells. I struggled with this for awhile by using this =Sum(AXX:AXX)-(AXX:AXX). This would come up with valueXX error. The only way I could get the program to understand what I was trying to subract is to add the other "SUM" in from the range.

=Sum(AXX:AXX)-SUM(AXX-AXX)

example: =SUM(C11:C15)-SUM(C19:C40)

[Villeroy]

=Sum(A1:A9)-(A1:A9) tries to subtract the 9 values A1:A9 from one value which is the sum of A1:A9.
If you enter that formula as an array formula, you get the 9 differences between the sum of all values and every single value. Controary to a normal spreadsheet formula, an array formula is able to return more than one value at once.

With values 1,2,3,...9 in A1:A9 and the formula entry finished with Ctrl+Shift+Enter instead of Enter, the formula result looks like this:
44
43
42
41
40
39
38
37
36

because the formula subtracts
45-1
45-2
45-3
and so on

P.S. the #VALUE! error indicates some problem with an inappropriate value, for instance when you try to do arithmetics with text values. In this particular case, nine result values do not fit into one cell unless you work with an array formula which is able to spread 9 results across 9 cells.