SOLVED

# Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

Copper Contributor

# Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

8 Replies
best response confirmed by Priyanshu1325 (Copper Contributor)
Solution

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

@Priyanshu1325 That would be 1000-100 + 1000-150 + 1000-300 + 1000-100 = 3350

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

B4-B5:B8 gives

1000-100

1000-150

1000-300

1000-100

after that you sum results

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

Thank you very much. This was very helpful. Then how would I go about subtracting these cells? Is there any way to subtract like we add by just writing the first and last cell (eg. =SUM(B5:B9)?

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

But won't this violate order of operations? How does excel read/interpret (B5:B9)?

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

@Priyanshu1325 Not sure what you mean but perhaps you need this:

=B4-SUM(B5:B9)

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

Thank you for the reply. What I meant was is there a specific formula for subtraction in Excel. For example if we want to add cells B4 through B9 we can do it by [=SUM(B4:B9)] or [=B4+B5+B6+B7+B8+B9]. But if we want to subtract the same cells do we have to type =B4-B5-B6-B7-B8-B9 every time? Is there a way to write it concisely like the SUM formula for addition? What if have to subtract multiple rows or columns of cells, do we have type each one of them?

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

@Priyanshu1325 Well then you have to enter the numbers in B5:B9 as negatives, and use SUM on the entire range B4:B9 or just the =B4-SUM(B5:B9) As I mentioned earlier.

# Re: Can someone please explain how B9 is calculating this answer [Formula is (=SUM(B4-(B5:B8))]?

Got it. Thank you so much.