summing new value over the previous value based on more than item

[Alaa mg]

hello
can I sum the new value in cell F6 over the prevouis value in cell F6 based on item in cell F2
F2 depends on column B , F6 depends on column C
when select A001 it will be

1.xlsx
	A	B	C	D	E	F
1		ITEM	COST			ITEM
2		A001	1000			A001
3		A002	1500
4		A003	2900
5		A004	100			TOTAL
6						1000
Sheet1

and when select different item should sum over previous value like this

1.xlsx
	A	B	C	D	E	F
1		ITEM	COST			ITEM
2		A001	1000			A002
3		A002	1500
4		A003	2900
5		A004	100			TOTAL
6						2500
Sheet1

 

[Sahak]

Hi Alaa,

Try this

Sub GetTotal()
    Dim WledVal As Long
    WledVal = Application.VLookup(Range("F2"), Range("B2:C5"), 2, 0)
    Range("F6").Value = Range("F6").Value + WledVal
End Sub

 

[Alex Blakenburg]

I might be misreading the requirement but if you just need a formula try this:

Book1
	A	B	C	D	E	F
1		ITEM	COST			ITEM
2		A001	1000			A003
3		A002	1500
4		A003	2900
5		A004	100			TOTAL
6						5400
Sheet1

Cell Formulas
Range	Formula
F6	F6	=SUM($C$2:INDEX($C$2:$C$5,MATCH($F$2,$B$2:$B$5,0)))

 

[Alaa mg]

@Sahak gives error mismatch in this line

 WledVal = Application.VLookup(Range("F2"), Range("B2:C5"), 2, 0)

 

[Alaa mg]

@Alex Blakenburg based on your picture and what I want . the value should be 2900 and if I select another like A001 then the summing is 2900+1000=3900 and if I clear the cell F2 should start from the beginning when sum .

 

[Alex Blakenburg]

The error you got with @Sahak's code indicates an invalid Item was entered into F2.

Here is a slightly more elaborate version to be copied into the Code Module of the sheet you are working on using a Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    Dim inputCell As Range, totalCell As Range, dataRng As Range
    Dim addnewVal As Double
    Dim lastRow As Long

    Set inputCell = Range("F2")
    Set totalCell = Range("F6")
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    Set dataRng = Range("B2:C" & lastRow)

    If Not Intersect(Target, inputCell) Is Nothing Then
        If inputCell = "" Then
            totalCell.Value = ""
        Else
            With Application
                If IsError(.VLookup(inputCell, dataRng, 2, False)) Then
                    MsgBox "Invalid Item Entered"
                Else
                    addnewVal = .IfError(.VLookup(inputCell, dataRng, 2, False), 0)
                    totalCell.Value = totalCell.Value + addnewVal
                End If
            End With
        End If

    End If
    Application.EnableEvents = True
End Sub

 

[Alaa mg]

@Alex Blakenburg fantastic ! just question , is it impossible by formula .

 

[Alex Blakenburg]

Because you want to access the previous value in the cell that has the formula in it, I don't believe any formula will work.
I don't know enough about the new Lambda function to know whether it might be able to handle it but to even try that you would need to be on MS 365 and be quite current in terms of installed updates.

 

[Alaa mg]

@Alex Blakenburg thanks very much for your solution .

 

[Alex Blakenburg]

You are welcome, glad we could help.