# Find Empty Cell and Sum Cells Above

#### Attachments

• 11.7 KB Views: 20

#### AlanSidman

##### Active Member
Code:
``````Option Explicit

Dim c As Range, rng As Range
Dim lr As Long, x As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
Set rng = Range("E3:E" & lr + 1)
x = 0
For Each c In rng
If c <> "" And c.Offset(0, -1) <> "" Then
x = x + c
ElseIf c = "" Then
c = x
x = 0
End If
Next c
End Sub``````

#### Kenshin

##### Member
Sorry Alan not clear from the beginning, but i need solution with formula

thank you

#### salim hasan

##### Member
try this formula in G6 and drag down
Code:
``=IFERROR(SUMPRODUCT(E3:E6,D3:D6^0),"")``

#### Attachments

• 16.7 KB Views: 13

#### Kenshin

##### Member
Thanks Salim but it doesnt work, I need start copy the formula from G3

#### salim hasan

##### Member
We can working with helper column like this file

#### Attachments

• 17 KB Views: 7

Hi:

May be a pivot?

Thanks

#### Attachments

• 14.7 KB Views: 5

#### bosco_yip

##### Excel Ninja
Maybe,

In G3, copied down :

=IF((C3="")*(C2<>""),SUM(E\$2:E2)-SUM(G\$2:G2),"")

Regards
Bosco

#### Kenshin

##### Member
Thanks Bosco, but could you revise your formula so i can use it on column E? And combine with my formula on Clumn E which is on E3 and go down = C3*D3?

thank you so much

#### Attachments

• 11.3 KB Views: 2

#### bosco_yip

##### Excel Ninja

In E3, copied down :

=IF(C3<>"",C3*D3,IF((C2<>"")*(C3=""),SUM(E\$2:E2)-2*SUMIFS(E\$2:E2,C\$2:C2,"="),""))

Regards
Bosco

#### Kenshin

##### Member
Oh my, that's work great, thank you again Bosco, God Bless you