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=""))
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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.
 

Forum statistics

Threads
1,141,924
Messages
5,709,372
Members
421,633
Latest member
Ubergribbler

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top