Find Empty Cell and Sum Cells Above

[Kenshin]

Please need help for this case, hope get the answer


thank in advanced

 

[AlanSidman]

Option Explicit

Sub AskHelp()
    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]

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

thank you

 

[AlanSidman]

Understand your need. Cannot help you. Hopefully, someone else can.

 

[salim hasan]

try this formula in G6 and drag down

=IFERROR(SUMPRODUCT(E3:E6,D3:D6^0),"")

 

[Kenshin]

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

 

[salim hasan]

We can working with helper column like this file

 

[Nebu]

Hi:

May be a pivot?

Thanks

 

[bosco_yip]

Maybe,



In G3, copied down :

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

Regards
Bosco

 

[Kenshin]

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

 

[bosco_yip]

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]

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