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

ExcelDropper

New Member
Joined
Oct 25, 2020
Messages
2
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,382
Office Version
  1. 2013
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,860
Messages
5,542,935
Members
410,577
Latest member
ZvK
Top