# SUM OFFSET function with Subtraction Results in #VALUE

#### boykinja

##### New Member
Hello,

I'm struggling to figure out why this error is appearing in my spreadsheet. I need to calculate the sum of multiple columns and an unknown number of rows. I have created the following formula but a #VALUE error is being generated for it.

=SUM((C2:OFFSET(C4,-1,0)),-(F2:OFFSET(F4,-1,0)))

If I remove the subtraction symbol between the two values than the formula calculates the sum without issue.

If I move the formula up one row, changing the values respectively, the formula works without issue with the subtraction included.
=SUM((C2:OFFSET(C3,-1,0)),-(F2:OFFSET(F3,-1,0)))

I've verified the blank cells have no value. Here's the evaluation for the formula right before and when it generates the error.

I am a bit of an excel noob, and I have been scratching my head over this for a few days. I apologize if the solution to this is obvious, but I haven't been able to find an adequate solution or information on how to solve this. Any help is greatly appreciated!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Does this work for you?

=SUM((C2:OFFSET(C4,-1,0)))-SUM(F2:OFFSET(F4,-1,0))

Regards,
Brian

That works! You're a life saver. Thanks for your help!

Replies
10
Views
177
Replies
0
Views
259
Replies
5
Views
146
Replies
2
Views
209
Replies
5
Views
107

1,196,497
Messages
6,015,554
Members
441,900
Latest member
Inaschemitex2023

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back