# sumproduct in vba

#### cmefly

##### Well-known Member
hi,

i'm trying to develop a code that searches column K for all occurrences of a certain string and adds up the associated values found in column I.

example: look for "def" within column K and and up column I

column i column K
1 abcdefghi
2 defghij
3 123defghijklm
4 _def_123
5 af;lkeion

any ideas on where to start????

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### cmefly

##### Well-known Member
i've got this but it's not working....HELP PLZ!

Code:
``````Sub testbi()
Dim y As Integer
y = Sheets("14. Analysis").Range("Z1").Value
x = 0

For Each cell In Sheets("4. schedule").Range("K49:k78").Cells
If InStr(1, cell, y & "def") = 0 Then
x = x + Sheets("4. schedule").Range("I" & cell.Row).Value
End If
Next cell

Range("B19") = x

End Sub``````

#### jim may

##### Well-known Member
Excel Workbook
IJKL
1111.00abcdefghi1,110.00
2222.00defghij
3333.00123defghijklm
4444.00_def_123
5555.00af;lkeion
Sheet1
Excel 2007
Cell Formulas
RangeFormula
L1=SUMIF(\$K\$1:\$K\$5,"*def*",I1:I5)

#### jim may

##### Well-known Member
The code equivalent is:

Code:
``````Sub Macro1()
Range("L1").FormulaR1C1 = "=SUMIF(C11,""*def*"",C9)"  '(modified for full columns)
End Sub``````

Replies
10
Views
261
Replies
3
Views
279
Replies
18
Views
512
Replies
0
Views
138
Replies
4
Views
307

1,195,623
Messages
6,010,748
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back