# VBA to expand a sum formula based on if there is a value in another cell

#### ExcelDropper

##### New Member
In a worksheet, I have 10 different sum formulas across a1:j1. The sum formula in a1 is =sum(a2:a5), while the sum formula in b1 is =sum(b2:b5), c1 is =sum(c2:c5) and so on for all the formulas. What I want to be able to do is expand on these formulas if there is a non-empty cell in a column or range. For example, let's say I have one value in z1 and thats all. The formulas will stay the same. But if i have a value in z1, and a value in z2, the sum formulas will expand. The sum formula in a1 is now =sum(a2:a5)+sum(a3:a6), while the sum formula in b1 is now =sum(b2:b5)+sum(b3:b6). If I had three values in column z, in z1, z2 and z3, my formulas would change again. Now a1 is =sum(a2:a5)+sum(a3:a6)+sum(a4:a7). If I removed two of the values and now only z1 has a value, it will become =sum(a2:a5) again after I click the macro.

Is this possible with VBA?

Thanks.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Michael M

##### Well-known Member
Put this in A1, then drag across as far as required
Excel Formula:
``=SUM(A2,(IF(COUNT(A3:A999)>0,OFFSET(A3,0,0,COUNT(A3:A999)),0)))``

Replies
3
Views
173
Replies
7
Views
70
Replies
4
Views
139
Replies
6
Views
104
Replies
3
Views
96

### Forum statistics

1,141,740
Messages
5,708,233
Members
421,553
Latest member
Geeyj ### 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.

### Which adblocker are you using?    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

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