COUNTIF with two arguements?

[Mr. Walnuts]

Ok in the sheet below, I am trying to use a COUNTIF function with two arguements. It doesn't seem to be working though. Is there a better way to do it? What I need is this... for example, in cell C14, I need a count of how many married male persons there are in this database at the top. In this instance, the result should be 3. The same thing applies to the rest of the blocks. you get the point I assume. If someone could clue me in to the right direction, I'd love you forever. By the way, this is just an example. the real deal is on a much larger scale with more important information.

Book1
	A	B	C	D
1	Name	Gender	Maritalstatus	ShoeSize
2	Joseph	M	Married	8
3	Thomas	M	Single	8
4	James	M	Single	8
5	Gary	M	Married	9
6	Jackie	F	Married	10
7	Herman	M	Married	8
8	Carmen	F	Single	9
9	Jessica	F	Married	7
10
11
12
13			Married	Single
14		Male
15		Female
16
17		Shoesize
18		8
19		9
Sheet1

 

[Richard Schollar]

Hi

=SUMPRODUCT(--($B$2:$B$9="M"),--($C$2:$C$9="Married"))

The criteria (ie "M" and "Married") can be cell references holding these values.

Richard

 

[Mr. Walnuts]

Out of curiosity... what is the "--" for in the formula? I understand the principle behind the formula, I just dont understand why it doesn't work without the dashes.

thank you by the way

 

[Legacy 51064]

This works as well:


=SUMPRODUCT(($B$2:$B$9="M")*($C$2:$C$9="Married"))

 

[Brian from Maui]

So does a Pivot Table...

 

[Richard Schollar]

The double negative sign basically forces Excel to convert the array of True/Falses to 1s and 0s. Equally, +0 or *1 could be used:

=SUMPRODUCT(($B$2:$B$9="M")+0,($C$2:$C$9="Married")+0)

=SUMPRODUCT(($B$2:$B$9="M")*1,($C$2:$C$9="Married")*1)

This works on the basis of:

True*1
True+0
--True

all equal 1 and

False*1
False+0
--False

all equal 0.

Hope this helps!

Richard