COUNTIF

[mpajenn]

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]

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

Bob Phillips explains =sumproduct() in much more detail here:
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.)

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[JLatham]

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

That should help some. To explain, the tests of D4145 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.