checking a formula for counting between parameteres across multiple columns

**anna57** · 11-17-2009, 10:06 AM

Hello,

I am trying to get a database to count how many cells in a column are between a range (26-40) if a date in one cell (D5:D1300) is earlier than a date in another (K2) and if the value in another cell (E5:E1300) is "open". I have done the formula below but it does not seem to be adding things together the right way even though it returns a number:

=SUMPRODUCT(--(Database!D5:D1300>=26),--(Database!D5:D1300<40) --(Database!E5:E1300="OPEN"))

Any ideas?

Anna

**NBVC** · 11-17-2009, 10:11 AM

Missing a comma:

=SUMPRODUCT(--(Database!D5:D1300>=26),--(Database!D5:D1300<40),--(Database!E5:E1300="OPEN"))

also you mention dates in D5:D1300. Is that correct because that is where you are checking the range of 26-40 too?

**darkyam** · 11-17-2009, 10:11 AM

You say dates are in D, but then use that to compare to 26-40. Assuming the dates are after February 1900 (or 1904, depending on your settings), this formula will return 0.