Why does my formula return zero?
 

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)))


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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

=ISNUMBER(C2)

Do you get TRUE for each one?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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.

--
Hope that helps!
TazGuy
XL2000 on Windows 2000


"was" wrote:


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)))


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

Try posting some sample data that exists in your sheet.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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

"was" wrote in message
...

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(200
5,3,31)),--(ISNUMBER(K2:K465)))


--
was
------------------------------------------------------------------------
was's Profile:
http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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.


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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

=ISNUMBER(C2)

and

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

What results do these formulas return?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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. Both
columns F and K are populated with dates. F and K are formated as date.
Any suggestions?


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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


--
was
------------------------------------------------------------------------
was's Profile: http://www.excelforum.com/member.php...o&userid=20211
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374096

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.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=374096