I am working on the following formula that returns 0 as the answer but when I
review the formula bar it has the correct answer? Is there anything I can do
to display the result in the cell or am I overwriting the calculation with
the 0 for the false statement somehow. I have changed the grouping but it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks

Try this normally entered formula:

=SUMPRODUCT(--(TEXT(YTD!$A$2:$A$20000,"mmmyyyy")="Feb2008"),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
I am working on the following formula that returns 0 as the answer but when
I
review the formula bar it has the correct answer? Is there anything I can
do
to display the result in the cell or am I overwriting the calculation with
the 0 for the false statement somehow. I have changed the grouping but it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks

Thanks for the suggestion, but the below will not work I need to use the date
range because the months lap over in the ranges.

Tera

"T. Valko" wrote:

Try this normally entered formula:

=SUMPRODUCT(--(TEXT(YTD!$A$2:$A$20000,"mmmyyyy")="Feb2008"),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
I am working on the following formula that returns 0 as the answer but when
I
review the formula bar it has the correct answer? Is there anything I can
do
to display the result in the cell or am I overwriting the calculation with
the 0 for the false statement somehow. I have changed the grouping but it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks

I need to use the date range because the months
lap over in the ranges.

I don't know what that means but in the formula you tried:

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

You're using a date range from 2/1/2008 to 2/29/2008 (inclusive). That's the
same date range the formula I suggested is using.


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
Thanks for the suggestion, but the below will not work I need to use the
date
range because the months lap over in the ranges.

Tera

"T. Valko" wrote:

Try this normally entered formula:

=SUMPRODUCT(--(TEXT(YTD!$A$2:$A$20000,"mmmyyyy")="Feb2008"),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
I am working on the following formula that returns 0 as the answer but
when
I
review the formula bar it has the correct answer? Is there anything I
can
do
to display the result in the cell or am I overwriting the calculation
with
the 0 for the false statement somehow. I have changed the grouping but
it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks

You are correct. That would help if it was just for February 2008. But I am
trying to do a summary table every month for Jan - Dec, where some of the
months might include a range of 2/25/08 thru 3/23/08 which will be March.

The formula below is an example and I am trying to workout the formula
needed to accomplish the result. In the formula bar the formula result is
correct, but when the answer is displayed in the cell it is 0. I researhed
further and the reason the formula does not work is because it is working
like an array, but I did not use Control+Shift+Enter. I have tried it but it
is still giving me the same answer. It is correct in the formula bar and the
result is still $0.

Can you help with calculating as an array?

"T. Valko" wrote:

I need to use the date range because the months
lap over in the ranges.

I don't know what that means but in the formula you tried:

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

You're using a date range from 2/1/2008 to 2/29/2008 (inclusive). That's the
same date range the formula I suggested is using.


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
Thanks for the suggestion, but the below will not work I need to use the
date
range because the months lap over in the ranges.

Tera

"T. Valko" wrote:

Try this normally entered formula:

=SUMPRODUCT(--(TEXT(YTD!$A$2:$A$20000,"mmmyyyy")="Feb2008"),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
I am working on the following formula that returns 0 as the answer but
when
I
review the formula bar it has the correct answer? Is there anything I
can
do
to display the result in the cell or am I overwriting the calculation
with
the 0 for the false statement somehow. I have changed the grouping but
it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks

You can do this without using an array formula. A lot of people have
"difficulty" with them which is why I suggested using the SUMPRODUCT formula
as opposed to your original array formula.

If you want to make it so the date range can be easily changed for
flexibilty use cells to hold the date boundaries:

include a range of 2/25/08 thru 3/23/08

H1 = 2/25/2008
I1 = 3/23/2008

=SUMPRODUCT(--(YTD!$A$2:$A$20000=H1),--(YTD!$A$2:$A$20000<=I1),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)

If you still get a result of 0 and know for certain that is incorrect then
you have data problems. The dates in column A may not be real Excel dates.
The values to sum may not be numeric numbers, they might be TEXT numbers.


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
You are correct. That would help if it was just for February 2008. But I
am
trying to do a summary table every month for Jan - Dec, where some of the
months might include a range of 2/25/08 thru 3/23/08 which will be March.

The formula below is an example and I am trying to workout the formula
needed to accomplish the result. In the formula bar the formula result is
correct, but when the answer is displayed in the cell it is 0. I
researhed
further and the reason the formula does not work is because it is working
like an array, but I did not use Control+Shift+Enter. I have tried it but
it
is still giving me the same answer. It is correct in the formula bar and
the
result is still $0.

Can you help with calculating as an array?

"T. Valko" wrote:

I need to use the date range because the months
lap over in the ranges.

I don't know what that means but in the formula you tried:

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

You're using a date range from 2/1/2008 to 2/29/2008 (inclusive). That's
the
same date range the formula I suggested is using.


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
Thanks for the suggestion, but the below will not work I need to use
the
date
range because the months lap over in the ranges.

Tera

"T. Valko" wrote:

Try this normally entered formula:

=SUMPRODUCT(--(TEXT(YTD!$A$2:$A$20000,"mmmyyyy")="Feb2008"),--(YTD!$B$2:$B$20000=A8),YTD!$F$2:$F$20000)


--
Biff
Microsoft Excel MVP


"teethomas" wrote in message
...
I am working on the following formula that returns 0 as the answer
but
when
I
review the formula bar it has the correct answer? Is there anything
I
can
do
to display the result in the cell or am I overwriting the
calculation
with
the 0 for the false statement somehow. I have changed the grouping
but
it
does not work.

=SUM(IF(YTD!$B$2:$B$20000=A8,IF(YTD!$A$2:$A$20000 =DATEVALUE("2/01/2008"),IF(YTD!$A$2:$A$20000<DATEVALUE("3/1/2008"),YTD!$F$2:$F$20000,0),0),0))

Thanks