CountIf multiple parameters - is it possible?

Thanks:  0
Likes:  0

# Thread: CountIf multiple parameters - is it possible?

1. ## 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
 D10 =

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. ## Re: CountIf multiple parameters - is it possible?

Why 3 as result for IBM?

3. ## 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. ## Re: CountIf multiple parameters - is it possible?

thanks that worked!!

5. ## Re: CountIf multiple parameters - is it possible?

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
 B11C11D11 =

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. ## 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. ## Re: CountIf multiple parameters - is it possible?

thanks guys!!

8. ## Re: CountIf multiple parameters - is it possible?

why not just a pivot table?

9. ## 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.

## User Tag List

#### Posting Permissions

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