1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Calling all Excel Experts

Discussion in 'Software' started by Nelix, Jul 10, 2006.

  1. Nelix
    Honorary Member

    Nelix Gigabyte Poster

    1,412
    3
    82
    Hi


    I'm no Excel expert but I consider myself to be fairly good using Excel, however I have a problem and would like your help:

    I am looking for a formula that will look at 2 columns and if the contents of these columns matches the set criteria then count it E.G

    =countif [Column A = "Network PC"] and [Column J = "expired"]

    I just don't know the syntax for the formula

    I know how to use the Count command to count instances of "Criteria" in a single column but not in multiple columns.

    Hope you can understand what I am trying to do

    thanks in advance

    Derek
     
    Certifications: A+, 70-210, 70-290, 70-291
    WIP: 70-294
  2. Mr.Cheeks

    Mr.Cheeks 1st ever Gold Member! Gold Member

    5,369
    85
    190
    :blink

    wanna do a rephrase? cuz we have a *BUMP*
     
  3. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    I'm not an Excel expert - but I'll see if there is anything in my books tomorrow at work.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  4. Sparky
    Highly Decorated Member Award

    Sparky Zettabyte Poster Moderator

    10,189
    296
    319
    How do you want the set criteria defined? By cell values or by an input box? I had to write something similar for a client in VB and I’ve dumped the code below. Basically it checks values in two columns, if they match then one was added to the value of what the actual match is, if not the next two values were compared. This was done until a blank cell was reached.

    I can revise the code if it helps or post the Excel spreadsheet as a link and I’ll plug the code in for you. :biggrin

    Code:
    
    Sub calculation()
    
    'Calculation Macro by Sparky
    
    Dim value As String
    Dim comparevalue As String
    Dim counter As Integer
    Dim whatplate As String
    Dim poly_single As Integer
    Dim preinked_black As Integer
    Dim preinked_red As Integer
    Dim preinked_blue As Integer
    Dim black_rubber As Integer
    
    
    counter = 0                             'reset counters
    poly_single = 0
    preinked_black = 0
    preinked_red = 0
    preinked_blue = 0
    black_rubber = 0
    
    Sheets("Details").Select                'select details sheet for calculations
    For Each cell In range("M1", "M10000")  'define range of cells to search
       
    
    If counter = 0 Then
        value = cell.value                  'set up values to compare
        comparevalue = cell.value
        counter = counter + 1
    Else
    counter = counter + 1                   'move forward to next cell for comparison
    value = cell.value
        If comparevalue = value Then
        comparevalue = cell.value           'if values are the same move to next cell
        Else
                                            'if not the same then check the plate type and add one
            whatplate = cell.Offset(0, -1).value    'check plate type
            
            If whatplate = "poly_single" Then
                poly_single = poly_single + 1
            End If
            
            If whatplate = "preinked_black" Then
                preinked_black = preinked_black + 1
            End If
            
            If whatplate = "preinked_red" Then
                preinked_red = preinked_black + 1
            End If
            
            If whatplate = "preinked_blue" Then
                preinked_blue = preinked_blue + 1
            End If
            
            If whatplate = "preinked_green" Then
                preinked_green = preinked_green + 1
            End If
            
            If whatplate = "black_rubber" Then
                black_rubber = black_rubber + 1
            End If
            
            comparevalue = cell.value              'move to next cell to compare
        
        End If
    End If
        
       
    If comparevalue = "" Then                       'exit loop if there is a blank cell
        Exit For
    End If
    
    
    Next
    
    Sheets("Summary").Select
    Worksheets(2).range("G6").value = poly_single                 'output values on Summary sheet
    Worksheets(2).range("G7").value = preinked_black
    Worksheets(2).range("G8").value = preinked_blue
    Worksheets(2).range("G9").value = preinked_red
    Worksheets(2).range("G10").value = preinked_green
    Worksheets(2).range("G11").value = black_rubber
    
    Worksheets(2).range("I9").value = "The Macro was last run on " & Date & " at " & Time
    
    
    
    
    
    
    
    
    
     
    Certifications: MSc MCSE MCSA:M MCSA:S MCITP:EA MCTS(x5) Security+ Network+ A+
    WIP: Exchange 2007\2010
  5. Arroryn
    Honorary Member

    Arroryn we're all dooooooomed

    4,009
    186
    209
    If you are just looking to match two certain values, could you use the 'Lookup' formula function?

    (I think VLOOKUP) is the one you're looking for. It references values between Column X and Column Y, and puts the results side by side. You can then happily sort to your heart's content!
     
    Certifications: A+, N+, MCDST, 70-410, 70-411
    WIP: Modern Languages BA
  6. Nelix
    Honorary Member

    Nelix Gigabyte Poster

    1,412
    3
    82
    Thanks for all your help and advice guys but I was hoping for something far more simpler. Let me try and re-phrase:

    I have a spreadsheet that contains over 800 entrys of machines (laptops and desktops) that need to be replaced during Fiscal Year 05/06 and 06/07, I have a small table at the top of the spreadsheet that I want to use to breakdown the numbers i.e. I want to show the number of desktops that are to be replaced during FY 05/06.

    In order to do this I want to be able to check the contents of 2 columns:

    column E contains the device type, either "Network PC" or "Network Portable". Column J contains the FY, either "05/06" or "06/07"

    Take a look at the attached:

    so if the contents of column E equals "Network PC" AND Column J equals 05/06 then B3(of attached table) = 1.. if column E equals "Network Portable" AND Column J equals 05/06 then B4 = 1... if column E equals "Network Portable" AND Column J equals 05/06 then B4 = 2.......... and so on

    Get the drift ????????

    Hope this is clearer



    Derek
     

    Attached Files:

    Certifications: A+, 70-210, 70-290, 70-291
    WIP: 70-294
  7. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    I've checked my books and drawn a blank. However the wider 'net has many examples of how to do this. I think this page is one of the clearer ones.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  8. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    Further to this - this is a classic example of why I don't like Excel much, and prefer to run this on a real database. :biggrin

    This sort of thing is trivial in SQL!

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  9. Sparky
    Highly Decorated Member Award

    Sparky Zettabyte Poster Moderator

    10,189
    296
    319
    Harry makes a good point, I used to code in VB and this kind of thing would take 5 mins!

    I’ve downloaded the spreadsheet and I’ll have a look tomorrow (still like the odd coding problem!) and then I’ll post it back here. 8)
     
    Certifications: MSc MCSE MCSA:M MCSA:S MCITP:EA MCTS(x5) Security+ Network+ A+
    WIP: Exchange 2007\2010
  10. r.h.lee

    r.h.lee Gigabyte Poster

    1,011
    52
    105
    Nelix,

    Can you show us the formatting of an Excel row? Feel free to modify the data to ficticious data to maintain data security. The attached workbook only has a single worksheet of Sheet 1 that has data in the cell ranges A1::C4, Sheet 2 is blank, and Sheet 3 is blank.
     
    Certifications: MCSE, MCP+I, MCP, CCNA, A+
    WIP: CCDA
  11. Nelix
    Honorary Member

    Nelix Gigabyte Poster

    1,412
    3
    82
    Here you go


    If you need anything more just let me know
     

    Attached Files:

    Certifications: A+, 70-210, 70-290, 70-291
    WIP: 70-294
  12. r.h.lee

    r.h.lee Gigabyte Poster

    1,011
    52
    105
    Nelix,

    Given the formatting of the Excel spreadsheet, here's my suggestion:
    1 ) For each row, calculate the Network PC and FY 05/06

    e.g. L10 =IF(AND((E10="Network PC"), (J10="05/06")), 1, 0)
    Copy and paste this equation into cell L11 and below in the column

    2 ) For each row, calculate the Network PC and FY 05/06 machine value

    e..g M10 =L10*K10
    Copy and paste this equation into cell M11 and below in the column

    3 ) For each row, calculate the Network Portable and FY 05/06

    e.g. N10 =IF(AND((E10="Network Portable"), (J10="05/06")), 1, 0)
    Copy and paste this equation into cell N11 and below in the column

    4 ) For reach row, calculate the Network Portable and FY 05/06 machine value

    e.g. O10 =N10*K10
    Copy and paste this equation into cell O11 and below in the column

    5 ) For each row, calculate the Network PC and FY 06/07

    e.g. P10 =IF(AND((E10="Network PC"), (J10="06/07")), 1, 0)
    Copy and paste this equation into cell P11 and below in the column

    6 ) For each row, calculate the Network PC and FY 06/07 machine value

    e.g. Q10 =P10*K10
    Copy and paste this equation into cell Q11 and below in the column

    7 ) For each row, calculate the Network Portable and FY 06/07

    e.g. R10 =IF(AND((E10="Network Portable"), (J10="06/07")), 1, 0)
    Copy and paste this equation into cell R11 and below in the column

    8 ) For each row, calculate the Network Portable and FY 06/07 machine value

    e.g. S10 =R10*K10
    Copy and paste this equation into cell S11 and below in the column

    Here's what all the above does.
    Column L = Network PC and FY 05/06
    Column M = Value of Network PC and FY 05/06
    Column N = Network Portable and FY 05/06
    Column O = Value of Network Portable and FY 05/06
    Column P = Network PC and FY 06/07
    Column Q = Value of Network PC and FY 06/07
    Column R = Network Portable and FY 06/07
    Column S = Value of Network Portable and FY 06/07

    So for your cell equations...
    1 ) C3 =sum(L10:MXX)
    This will simply add up the numbers of each Network PC and FY 05/06. The "XX" part refers to the last row of data.
    2 ) B3 =sum(M10:LXX)
    This will simply add up the costs of each Network PC and FY 05/06. The "XX" part refers to the last row of data.
    3 ) C4 =sum(N10:NXX)
    This will simply add up the numbers of each Network Portable and FY 05/06. The "XX" part refers to the last row of data.
    4 ) B4 =sum(O10:OXX)
    This will simply add up the costs of each Network Portable and FY 05/06. The "XX" part refers to the last row of data.
    5 ) E3 =sum(P10:PXX)
    This will simply add up the numbers of each Network PC and FY 06/07. The "XX" part refers to the last row of data.
    6 ) D3 =sum(Q10:QXX)
    This will simply add up the costs of each Network PC and FY 06/07. The "XX" part refers to the last row of data.
    7 ) E4 =sum(R10:RXX)
    This will simply add up the numbers of each Network Portable and FY 06/07. The "XX" part refers to the last row of data.
    8 ) D4 =sum(S10:SXX)
    This will simply add up the costs of each Network Portable and FY 06/07. The "XX" part refers to the last row of data.

    I hope this helps.
     
    Certifications: MCSE, MCP+I, MCP, CCNA, A+
    WIP: CCDA
  13. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    And this could be done with a one-liner in SQL! :biggrin

    Harry (running like mad)
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  14. Nelix
    Honorary Member

    Nelix Gigabyte Poster

    1,412
    3
    82
    The boss like's his spreadsheets......everything he says starts with

    "can you just pop it in a spreadsheet...."

    Run faster Harry I'm catching up :aaah

    Thanks for all the help guys

    Derek
     
    Certifications: A+, 70-210, 70-290, 70-291
    WIP: 70-294
  15. Mr.Cheeks

    Mr.Cheeks 1st ever Gold Member! Gold Member

    5,369
    85
    190
    wanna know why managers like spreadsheets so much? cuz all the formulaes looks ever so complicated and when people look at them the screen, and thinks :blink *makes the manager look clever* ...well that is what my old manager use to do

    anyway, glad you sorted it out mate...
     

Share This Page

Loading...