Work Project

[ArsenalFan1]

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.

 

[lozzablake]

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]

Welcome to the Board!

You can use SUMIF:

Book1
	A	B	C	D
1	Company		$
2	CompanyXYZ	2	$10.00
3	ComapnyYS	1	$11.00
4	CompanyXYZ	3	$10.00
5	ComapnyYS	2	$9.00
6	CompanyXYZ	3	$12.00
7
8	CompanyXYZ		$32.00
9	ComapnyYS		$20.00
Sheet1

Hope that helps,

Smitty

 

[ArsenalFan1]

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]

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]

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]

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

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

 

[Andrew Poulsom]

You can use wildcards in SUMIF:

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