COUNTIF
 

In one column I have "M" or "W" and in the next column I have # representing
shoe sizes. To get a shoe count I put in the formula
=countif(d4:d145,m)+countif(e4:e145,#), (the # sign represents the shoe size
I am trying to count). The formula works good except if a shoe size falls
into both the m and w category it shows up in both counts instead of just the
one. I have tried putting "" marks around the M or W but it does not help.
Anyone have any ideas?

Jenn

COUNTIF
 

=sumproduct(--(d4:d145="M"),--(e4:e145=9))

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

(Ps. I used a size of 9.)



mpajenn wrote:

In one column I have "M" or "W" and in the next column I have # representing
shoe sizes. To get a shoe count I put in the formula
=countif(d4:d145,m)+countif(e4:e145,#), (the # sign represents the shoe size
I am trying to count). The formula works good except if a shoe size falls
into both the m and w category it shows up in both counts instead of just the
one. I have tried putting "" marks around the M or W but it does not help.
Anyone have any ideas?

Jenn

--

Dave Peterson

COUNTIF
 

pps.

If you're using xl2007, look at =countifs() in excel's help.

And more info.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

mpajenn wrote:

In one column I have "M" or "W" and in the next column I have # representing
shoe sizes. To get a shoe count I put in the formula
=countif(d4:d145,m)+countif(e4:e145,#), (the # sign represents the shoe size
I am trying to count). The formula works good except if a shoe size falls
into both the m and w category it shows up in both counts instead of just the
one. I have tried putting "" marks around the M or W but it does not help.
Anyone have any ideas?

Jenn

--

Dave Peterson

COUNTIF
 

=SUMPRODUCT(--(D4:D145="M"),--(E4:E145=#))
and for the ladies
=SUMPRODUCT(--(D4:D145="W"),--(E4:E145=#))

That should help some. To explain, the tests of D4:D145 work row by row and
each one is evaluated either true (1) or false(0). The results for that row
are multiplied by one another and that result is kept in a running total.
For any given row you have possible results:
0 * 1 = 0
1 * 0 = 0
0 * 0 = 0
1 * 1 = 1
so only when both conditions are met do you get a non-zero value to keep a
running total of.

Try it, you'll like it.
"mpajenn" wrote:

In one column I have "M" or "W" and in the next column I have # representing
shoe sizes. To get a shoe count I put in the formula
=countif(d4:d145,m)+countif(e4:e145,#), (the # sign represents the shoe size
I am trying to count). The formula works good except if a shoe size falls
into both the m and w category it shows up in both counts instead of just the
one. I have tried putting "" marks around the M or W but it does not help.
Anyone have any ideas?

Jenn