CountIf multiple parameters - is it possible?

[theDrew]

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!!

Pasta1.xls
	A	B	C	D
1	Company	Response1	Response2	Response3
2	IBM	1	0	0
3	HP	0	1	0
4	IBM	0	1	0
5	IBM	1	0	0
6	Dell	0	0	1
7	Dell	0	0	1
8	IBM	0	1	0
9	Dell	1	0	0
10	Total	????
Plan3

 

[Aladin Akyurek]

Why 3 as result for IBM?

 

[Andrew Poulsom]

Use SUMPRODUCT like this:

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

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

 

[theDrew]

thanks that worked!! :wink:

 

[Aladin Akyurek]

thanks that worked!! :wink:

If so, I'd suggest using a SumIf formula that suits your set up...

Book2
	A	B	C	D	E
1	Company	Response1	Response2	Response3
2	IBM	1	0	0
3	HP	0	1	0
4	IBM	0	1	0
5	IBM	1	0	0
6	Dell	0	0	1
7	Dell	0	0	1
8	IBM	0	1	0
9	Dell	1	0	0
10
11	IBM	2	2	0
12
Sheet1

B11:

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

which is copied across.

 

[Mark W.]

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

 

[theDrew]

thanks guys!!

 

[PaddyD]

why not just a pivot table?

 

[theDrew]

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.