Nested If Statement

[teethomas]

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

 

[T. Valko]

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)

 

[teethomas]

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]

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.

 

[teethomas]

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]

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.

 

[teethomas]

Thanks for the response. I had figured it out and got it to work shortly
after the last posting. This is the first time I have logged back into the
system to see your suggestion and to add the correction.

 

[T. Valko]

You're welcome. Thanks for the feedback!