dynamic sum function

[AliBb]

I have the following table I want the O/S column to calculate Total - sum(C4:?4)

? is determined based on the date in A2. In this example A4-sum(C4:C4) = 9000

If A2 = 31/8/18 then the formula would be A4-SUM(C4:D4).

How do I make this range dynamic?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]31/7/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Total[/TD]
[TD]O/S[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sept-18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10000[/TD]
[TD]9000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

Using your posted example, try this regular formula:

B4: =A4-SUMIF($C$3:$N$3,"<="&$A$2,$C4:$N4)

Is that something you can work with?

 

[AliBb]

Thank you