COUNTIF with two arguements?

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
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
ABCD
1NameGenderMaritalstatusShoeSize
2JosephMMarried8
3ThomasMSingle8
4JamesMSingle8
5GaryMMarried9
6JackieFMarried10
7HermanMMarried8
8CarmenFSingle9
9JessicaFMarried7
10
11
12
13MarriedSingle
14Male
15Female
16
17Shoesize
188
199
Sheet1
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Aug 8, 2005
Messages
176
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
 
L

Legacy 51064

Guest
This works as well:


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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top