excel 2003-countif statement

[Guest]

How do I write this statement (or another appropriate one) to count the
number of entries that meet two criteria? The data is contained in two
different columns.

Example - what is the # of entries that contain the number 2 in a specific
range in column C AND the word "new" in a specific range in column D.

 

[Guest]

Using an array formula you can get the counts that you need. Type the
following formula, modifying the ranges in columns C & D as needed:

=SUM(IF((C1:C34=2)+(D134="New")=2,1,0))

But don't press the Enter key, press Ctrl + Alt + Enter to make the formula
an array.

Should you modify the formula you will need to press Ctrl + Alt + Enter
after the edit to retain teh array status of the formula

 

[Dave Peterson]

Another version:

=sumproduct(--(c1:c10=2),--(d1:d10="new"))

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

 

[Guest]

Hey Dave, thanks for the link. I was able to read about the SUMPRODUCT
function without my eyes glazing over. Great information...

 

[Dave Peterson]

MEGO?

Sounds like a problem with every meeting I've attended.