# Work Project

#### ArsenalFan1

##### New Member
Hi,

This is my first post. Any help would be appreciated.

I have some information in lets say Sheet3

COL1 COL2 COL3
ROW1 CompanyXYZ 002 \$10
ROW2 ComapnyYS 001 11
ROW3 XYZ Company 003 10
ROW4 YS 002 9
ROW5 XYZ 003 12

In sheet1 in Range("A2) I want to put the sum of company lets say XYZ added together. The values are in Col3. So I want to search XYZ in Sheet3, add 10 + 10 + 12 and put 32 in sheet1.

But there is also a possibility that XYZ is not present in sheet3 then I want to put 0 in sheet1.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi. Welcome to the Board.

You can use SUMIF. There is a Conditional sum wizard that you can use for this Tools->Wizard->Conditional Sum. If you don;'t have this you need to tick the relevant add-in under Tools->Add-Ins first

Welcome to the Board!

You can use SUMIF:
Book1
ABCD
1Company\$
2CompanyXYZ2\$10.00
3ComapnyYS1\$11.00
4CompanyXYZ3\$10.00
5ComapnyYS2\$9.00
6CompanyXYZ3\$12.00
7
8CompanyXYZ\$32.00
9ComapnyYS\$20.00
Sheet1

Hope that helps,

Smitty

Thanks but there are no guarantees that it will be CompanyXYZ it could be XYZ corp or Location A - XYZ.

I am trying to use VB and the Cells.find function so i can make a button, press it then all the amounts relating to XYZ (in any form) will appear in a designated cell. Later i will expand this to put in any company i want (in a textbox) and which cell to put the value in.

That's a bit beyond what you mentioned first.

Smitty

Sorry for not being clear the first time
Maybe the format messed it up. My intention was to show:

COL1---------COL2-------------COL3--------COL4
ROW1--------CompanyXYZ----002----------\$10
ROW2--------ComapnyYS------001-----------11
ROW3--------XYZ Company----003-----------10
ROW4--------YS-----------------002-------------9
ROW5--------XYZ---------------003------------12

Here is the code i have copied from here so far
It helps me identify which row the first XYZ is or if it is not.

Sub Tester()
Dim strFound As String
On Error Resume Next

strFound = Cells.Find(What:="XYZ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Range("A1") = Cells.Find(What:="XYZ", After:=ActiveCell, LookIn:=xlValues, LookAt:= _xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row

Cells.Find(What:="hello", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

On Error GoTo 0
If strFound = vbNullString Then
End If

End Sub

Now I am trying to find the next row then maybe i can use the row numbers to get the amounts and add them together.

I apologize again for not being clear.

Here is something you can do. You could add an inputbox to get the search string from the user or add a loop to read in a list of items from a worksheet. You could also put the total in a cell rather than in a message box

Code:
``````Sub AddUp()

Dim rngTarget As Range
Dim rngFound As Range
Dim dblTotal As Double
Dim intLastRow As Integer
Dim intFirstRow As Integer

With Sheets("sheet2")  'change for your sheet name
intLastRow = .Cells(65536, 1).End(xlUp).Row
'set range to search
Set rngTarget = .Range(Cells(2, 1), Cells(intLastRow, 1))
'do search
Set rngFound = rngTarget.Find("XYZ")

'check if found something
If Not rngFound Is Nothing Then
'record first row
intFirstRow = rngFound.Row
'start running total
dblTotal = dblTotal + .Cells(rngFound.Row, 3)

'now search again until back to begining
Do
Set rngFound = rngTarget.FindNext(rngFound)
'check to make sure not double counting
If rngFound.Row <> intFirstRow Then
dblTotal = dblTotal + .Cells(rngFound.Row, 3)
End If

Loop Until rngFound.Row = intFirstRow

End If

End With

MsgBox "total for XYZ= " & dblTotal

End Sub``````

You can use wildcards in SUMIF:

=SUMIF(A1:A5,"*XYZ*",C1:C5)

Replies
2
Views
839
Replies
15
Views
586
Replies
12
Views
1K
Replies
6
Views
667
Replies
3
Views
402

1,211,886
Messages
6,104,576
Members
447,917
Latest member
cbacba

### 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