Calling all Excel Experts

Nelix · Jul 10, 2006

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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

Mr.Cheeks · Jul 10, 2006

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

wanna do a rephrase? cuz we have a *BUMP*

hbroomhall · Jul 10, 2006

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

Harry.

Sparky · Jul 10, 2006

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 Ive 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 Ill 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

Arroryn · Jul 11, 2006

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!

Nelix · Jul 11, 2006

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

hbroomhall · Jul 11, 2006

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.

hbroomhall · Jul 11, 2006

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.

This sort of thing is trivial in SQL!

Harry.

Sparky · Jul 11, 2006

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

Ive downloaded the spreadsheet and Ill have a look tomorrow (still like the odd coding problem!) and then Ill post it back here.

r.h.lee · Jul 11, 2006

Nelix said:

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
Click to expand...

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.

Nelix · Jul 12, 2006

Here you go

If you need anything more just let me know

r.h.lee · Jul 12, 2006

Nelix said:

Here you go

If you need anything more just let me know
Click to expand...

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.

hbroomhall · Jul 12, 2006

And this could be done with a one-liner in SQL!

Harry (running like mad)

Nelix · Jul 13, 2006

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

Thanks for all the help guys

Derek

Mr.Cheeks · Jul 13, 2006

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 *makes the manager look clever* ...well that is what my old manager use to do

anyway, glad you sorted it out mate...

Log in or Sign up

Calling all Excel Experts

Nelix Gigabyte Poster

Mr.Cheeks 1st ever Gold Member! Gold Member

hbroomhall Petabyte Poster Gold Member

Sparky Zettabyte Poster Moderator

Arroryn we're all dooooooomed Moderator

Nelix Gigabyte Poster

Attached Files:

Book2.xls

hbroomhall Petabyte Poster Gold Member

hbroomhall Petabyte Poster Gold Member

Sparky Zettabyte Poster Moderator

r.h.lee Gigabyte Poster

Nelix Gigabyte Poster

Attached Files:

Book2.xls

r.h.lee Gigabyte Poster

hbroomhall Petabyte Poster Gold Member

Nelix Gigabyte Poster

Mr.Cheeks 1st ever Gold Member! Gold Member

Share This Page

Navigation

Popular Forums

Useful Links

Log in or Sign up

Calling all Excel Experts

Nelix Gigabyte Poster

Mr.Cheeks 1st ever Gold Member! Gold Member

hbroomhall Petabyte Poster Gold Member

Sparky Zettabyte Poster Moderator

Arroryn we're all dooooooomed Moderator

Nelix Gigabyte Poster

Attached Files:

Book2.xls

hbroomhall Petabyte Poster Gold Member

hbroomhall Petabyte Poster Gold Member

Sparky Zettabyte Poster Moderator

r.h.lee Gigabyte Poster

Nelix Gigabyte Poster

Attached Files:

Book2.xls

r.h.lee Gigabyte Poster

hbroomhall Petabyte Poster Gold Member

Nelix Gigabyte Poster

Mr.Cheeks 1st ever Gold Member! Gold Member

Share This Page

Useful Searches