Find cell with text and count next cell number

2k05gt

Board Regular
Joined
Sep 1, 2006
Messages
151
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=""))
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
Try the sumif command. Help will revel more but I think it would look like this:

Code:
=sumif(cell1range,"COMPLETED",Cell2range)

Good luck

Hayden
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top