What's wrong with my formula? Excel 2000.

[Guest]

Sheet 1 has an X column that has dates. It also has column BD that has a
"Yes" or "No" entered in it. The cell range is from 2-221. On sheet 2, I
want to count the number of "no" entries in BD for only the records where the
date in X is greater than 1/1/2006.

My formula is this :
=COUNTIF(Sheet1!X2:X221,">1/1/2006",Sheet1!BD2:BD221,"no")

I can get the countif function to do each separately, but I don't know how
to combine the two.

Help!

Thank you.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(Sheet1!X2:X221>DATE(2006,1,1)),--(Sheet1!DB2B221="no"))

Or, it's better (more versatile) if you use cells to hold the criteria:

A1 = 1/1/2006
A2 = no

=SUMPRODUCT(--(Sheet1!X2:X221>A1),--(Sheet1!DB2B221=A2))

Biff

 

[Guest]

=SUMPRODUCT(--(Sheet1!X2:X221>--"1/1/2006"),--(Sheet1!BD2:BD221="no"))