Results 1 to 6 of 6

Countif multiple variables

This is a discussion on Countif multiple variables within the Excel Questions forums, part of the Question Forums category; Hey all, I've been struggling with this formule for some time. I'm inventarising things and would like a formula that ...

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    6

    Talking Countif multiple variables

    Hey all, I've been struggling with this formule for some time.
    I'm inventarising things and would like a formula that can tell me how many computers should be updated.

    In B2 to B260 are MS Office versions, C2 - C260 Adobe Reader, D2 - D260 Internet Explorer. Now I would like to know how many computers are not fully updated.

    =MAX(B2:D260) did not help, because there are computers with bad office version and good Adobe Reader, but also vice versa. =Countif didn't do the job too, because it only saw computers that had all three programs outdated.

    Can you help with this MrExcel forum? Thanks in advance.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,411

    Default Re: Countif multiple variables

    Here is a formula that will tell you the number of machines that are up to date
    assuming Win7, Adobe9 and IE8 are the standards. Note this is an array formula that must be entered using Control+Shift+Enter.

    Sheet1

    ABCD
    1MachineMS OSAdobeIE
    2AXP87
    3EWin798
    4BXP98
    5CWin778
    6DWin788
    7 1

    Spreadsheet Formulas
    CellFormula
    D7{=SUM((B2:B6="Win7")*(C2:C6=9)*(D2:D6=8)*(A2:A6<>""))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Alan Sidman
    Win 7--Office XP, 2007, 2010


  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    6

    Default Re: Countif multiple variables

    Dear Alansidman,

    Tried your formule and it works just fine in the test document, but in my own one it doesn't. Mind me uploading ( a part ) of it so you can see what you can do? Thanks in advance

    Hardware in TOPdeskOffice VersionAdobe ReaderInternet Explorer
    Team LeaderBAKLT5201200378
    name1BAKLT5069200098
    name2
    name3BAKLT5272200098
    name4BAKLT5271200396
    name5BAKLT5273200378
    name6BAKLT5036200398
    name7SLALT7367200096
    name8BARLT5276200398
    name9xxxx
    name10xxxx
    name11xxxx
    name12xxxx
    name13xxxx
    name14xxxx

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,411

    Default Re: Countif multiple variables

    What did not work? Did you use Control+Shift+Enter to enter the formula in the cell you are looking to populate with the answer?
    Alan Sidman
    Win 7--Office XP, 2007, 2010


  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,411

    Default Re: Countif multiple variables

    Took your data and tried two different formulae on it and it comes out for me.

    Sheet2

     ABCDE
    1MachineHardwareOfficeAdobeIE
    2TeamLeaderBAKLT5201200378
    3name1BAKLT5069200098
    4name2    
    5name3BAKLT5272200098
    6name4BAKLT5271200396
    7name5BAKLT5273200378
    8name6BAKLT5036200398
    9name7SLALT7367200096
    10name8BARLT5276200398
    11    2
    12    2

    Spreadsheet Formulas
    CellFormula
    E11{=SUM((C2:C10=2003)*(D2:D10=9)*(E2:E10=8)*(A2:A10<>""))}
    E12{=SUM(IF($D$2:$D$10=9,IF($E$2:$E$10=8,IF($C$2:$C$10=2003,1,0),0),0))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Alan Sidman
    Win 7--Office XP, 2007, 2010


  6. #6
    New Member
    Join Date
    Dec 2010
    Posts
    6

    Default Re: Countif multiple variables

    This works perfectly, thanks a lot!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com