Why does my formula return zero?

[was]

I am attempting to create a formula where column "C" has locations
represented by numbers. Column "F" is estimated completion dates.
Column "K" is the actual completion date. I would like to count the
dates in (column "K") by location (column "C"). And by month (column
"F"). I tried the following formula but it returns 0. The correct
answer is 6. Could someone please take a look at this and tell me what
I am doing wrong?

SUMPRODUCT(--(C2:C465=1700),--(F2:F465>=DATE(2005,3,1)),--(F2:F465<=DATE(2005,3,31)),--(ISNUMBER(K2:K465)))

 

[Domenic]

Make sure that each column contains numerical values (real numbers), no
text values. For each column, try...

=ISNUMBER(C2)

Do you get TRUE for each one

 

[Guest]

How about something like this?:

=SUMPRODUCT(--($C$2:$C$465=1700),--($F$2:$F$465>=DATE(2005,3,1)),--($F$2:$F$465<=(DATE(2005,3+1,1)-1)),--($K$2:$K$465<>""))

Checks to make sure the location is 1700, and the date is greater than 3/1
and less than 4/1 - 1 (3/31), and that your completion date isn't blank.

This would also work if your location and month to lookup were named ranges,
or in some other cell, so you wouldn't have to hardcode them.

 

[mangesh_yadav]

Try posting some sample data that exists in your sheet.

Mangesh

 

[Jim May]

A small example I set up and tried - worked for me.

 

[was]

A | B | C | D | E | F
| K

EC#| AF | C/C | EX | EFFECT | ENG. PLAN | ENG.
RELEASE

664 | AF-8 |1800 | Y | AF8,9,10 | 6/14/2005 |
653 | AF-8 |1800 | Y | AF8,9 | 4/18/2005 |
5/12/2005
718 | AF-8 |1700 | Y | AF8,9,10 | 7/8/2005 |
651 | AF-9 |1800 | Y | AF8,9 | 4/20/2005 |
5/12/2005

I checked to see if column C was formated as number as sugested. It
is.
This sheet tracks military aircraft engineering. I am trying to count
engineering releases (column K), by cost center (column C), and by
month (column F). There are blank cells in columns F and K. I used
autofilter on all column headers as this is a large sheet.
I can't seem to get this to space out right after it's submitted. I
hope you can make it out.

 

[Domenic]

While a cell may be formatted as General or Number, it's value may no
be recognized as a numerical value. Try the following formulas fo
each relevant column...

=ISNUMBER(C2)

and

=SUMPRODUCT(--ISNUMBER(C2:C465))=ROWS(C2:C465)

What results do these formulas return

 

[was]

I used the SUMPRODUCT formula on columns C,F, and K. All returned FALSE
I tried to format column C as number and it still returns FALSE. Bot
columns F and K are populated with dates. F and K are formated as date
Any suggestions

 

[Domenic]

Try the following...

1) Select/highlight an empty cell

2) Edit > Copy

3) Select/highlight Column C

4) Edit > Paste Special > Add > Ok

You may need to repeat this for your other columns. Does this help?

 

[was]

I gave it a try. When I attempted to do the paste special the progra
quit responding. I had to use task manager to shut it down. I the
copied the data to a new sheet with no formating. I was able to ge
column C to be TRUE per the ISNUMBER formula you gave me. I can't ge
columns F or K to be true. My SUMPRODUCT formula still returnes 0. I'
using Excel 2003. Do you have any ideas on what is going on

 

[Domenic]

If both these formulas return FALSE...

=ISNUMBER(F2)

and

=ISNUMBER(K2)

...then that means that the numbers are not being recognized as true
numbers (numerical values). You'll need to coerce those values into
true numbers by following the procedure I outlined. If your program
crashes, try doing it a small section at a time. If you continue to
have problems, you can always email me a copy of your file and I'll see
if I can pinpoint the problem. If you'd like me to take a look at it,
you can email me at (e-mail address removed)

 

[Domenic]

It seems that the reason the formula returns a zero value is that there
are no records that meet its criteria. While you have a number of
records whose value for Column C equals 1700, no corresponding cell in
Column F has a March, 2005, date. When I change the criteria for the
date in the formula to 5/1/2005 - 5/31/2005, the formula returns 1,
which seems to be correct. So the formula seems to be working fine.