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

ExcelDropper

New Member
Joined
Oct 25, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,919
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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)))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top