Results 1 to 9 of 9

CountIf multiple parameters - is it possible?

This is a discussion on CountIf multiple parameters - is it possible? within the Excel Questions forums, part of the Question Forums category; I have inherited an Excel database that looks somethign like this. I can't change the structure of the database since ...

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    104

    Default CountIf multiple parameters - is it possible?

    I have inherited an Excel database that looks somethign like this. I can't change the structure of the database since employees in the field are currently using it on a project.

    The employee enters a company name in the first column. In the response column, the employee enters a 1 for the correponding response, and all other responses are marked as 0. In the field marked ???? I need to return a number such as "How many employees entered IBM as a company and 3 as a response?" The only function that I can think to use is CountIf, but how can I enter multiple parameters with CountIf? I could use VBA but the list of companies and possible responses is huge.

    and while I am here, anyone know why those two tab characters are showing in C10 and D10?

    Thanks!!

    ******** ******************** ************************************************************************>
    Microsoft Excel - Pasta1.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    CompanyResponse1Response2Response3
    2
    IBM100
    3
    HP010
    4
    IBM010
    5
    IBM100
    6
    Dell001
    7
    Dell001
    8
    IBM010
    9
    Dell100
    10
    Total????**
    Plan3*

    [HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,354

    Default Re: CountIf multiple parameters - is it possible?

    Why 3 as result for IBM?

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,464

    Default Re: CountIf multiple parameters - is it possible?

    Use SUMPRODUCT like this:

    =SUMPRODUCT((A1:A9="IBM")*(B1:B9=1))

    You can replace the values after the = with a cell reference.

  4. #4
    Board Regular
    Join Date
    May 2003
    Posts
    104

    Default Re: CountIf multiple parameters - is it possible?

    thanks that worked!!

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,354

    Default Re: CountIf multiple parameters - is it possible?

    Quote Originally Posted by theDrew
    thanks that worked!!
    If so, I'd suggest using a SumIf formula that suits your set up...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    CompanyResponse1Response2Response3*
    2
    IBM100*
    3
    HP010*
    4
    IBM010*
    5
    IBM100*
    6
    Dell001*
    7
    Dell001*
    8
    IBM010*
    9
    Dell100*
    10
    *****
    11
    IBM220*
    12
    *****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    B11:

    =SUMIF($A$2:$A$9,$A11,B2:B9)

    which is copied across.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: CountIf multiple parameters - is it possible?

    [quote="theDrew"]...The only function that I can think to use is CountIf, but how can I enter multiple parameters with CountIf? ...quote]

    Use...

    {=COUNT(IF(condition1,IF(condition2,...,1)))}

    ...or, simply use the Conditional Sum Wizard to construct your formula.

  7. #7
    Board Regular
    Join Date
    May 2003
    Posts
    104

    Default Re: CountIf multiple parameters - is it possible?

    thanks guys!!

  8. #8
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: CountIf multiple parameters - is it possible?

    why not just a pivot table?

  9. #9
    Board Regular
    Join Date
    May 2003
    Posts
    104

    Default Re: CountIf multiple parameters - is it possible?

    actually I am making dummy variables so that I CAN build a pivot table. The workbook has a huge number of columns and rows, and I am building the dummy variables to compact the information as much as possible before putting it into a pivot table. Otherwise, it looks very disorganized and unprofessional.

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