Find cell with text and count next cell number

[2k05gt]

Cell1 Cell2
25 25
FULL 20
COMPLETED 200
25 25
25 25
COMPLETED 40


I need to find the word COMPLETED in all cell1 then add up the figure in cell2

there are hundreds of rows I tried Find and Counif with find, but got lost after this.

=SUMPRODUCT(--(LEFT(B2:B2135,7)="completed"), Sum(C2:C2135=""))

 

[hayden]

Try the sumif command. Help will revel more but I think it would look like this:

=sumif(cell1range,"COMPLETED",Cell2range)

Good luck

Hayden

 

[2k05gt]

Wow Hayden,
that was it. Man how easy that was

 

[PolarBear]

Sub AddEmUp()



Dim CompleteCount As Long
Dim StartRow As Long
Dim EndRow As Long
Dim i As Long
Dim StartColumn As String
Dim CompText As String

CompleteCount = 0

StartRow = Application.InputBox("Enter the Start Row")
EndRow = Application.InputBox("Enter the End Row")
StartColumn = Application.InputBox("Enter the first column of data")

For i = StartRow To EndRow
     CompText = Range(StartColumn & i).Value
     If InStr(CompText, "COMPLETED") > 0 Then CompleteCount = CompleteCount + Range(StartColumn & i).Offset(0, 1).Value
Next i

' you don't specify where you want the number to go; you can either write it to the sheet,
' or popup a message box with the total.
MsgBox (CompleteCount)

End Sub

This is a different take on the problem, but you don't have to copy any formulaes into your sheet.