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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

ArsenalFan1

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

That's a bit beyond what you mentioned first.

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

Smitty
 

ArsenalFan1

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

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
 

Forum statistics

Threads
1,136,652
Messages
5,677,008
Members
419,668
Latest member
DharmaK

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