Work Project

ArsenalFan1

New Member
Joined
Sep 18, 2006
Messages
8
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.

Thanks for your time.
 

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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
That's a bit beyond what you mentioned first.

I'd start with the InStr function. I.E. If InStr(c, "XYZ") Then...

Smitty
 
Upvote 0
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
MsgBox "Not Found"
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top