Count based on Conditions

[shyamgnair]

Hi,
I have two columns Defect Type & Status in an excel sheet. I need to arrive
at a summary based on the various values that are avaialble inthese columns.
See the below table
Defect Type Status
====================
Defect Works as Designed
Defect Duplicate
Defect Duplicate
Defect Duplicate
Defect Works as Designed
Defect Duplicate
Defect Works as Designed
Defect Duplicate
Defect Fixed
Defect Fixed
Defect Works as Designed
Document Open
Document Open

I need to get the count for the following

Defect + Works as Designed = X nos
Defect + Fixed = X nos
Document + Works as Designed = X nos.

Is there any formula in excel which can do the same?

 

[Stefi]

Defect + Works as Designed = X nos
=SUMPRODUCT(--($A$2:$A$14="Defect"),--($B$2:$B$14="Works as Designed"))

Defect + Fixed = X nos =SUMPRODUCT(--($A$2:$A$14="Defect"),--($B$2:$B$14="Fixed"))

Document + Works as Designed = X nos.

=SUMPRODUCT(--($A$2:$A$14="Document"),--($B$2:$B$14="Works as Designed"))

Regards,
Stefi