SUMPRODUCT

I am using the formula below and it seems to work fine.

As an array:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--('Enroll
I'!$F$2:$F$2741Q1),--('Enroll I'!$N$2:$N$2741="No Planned Data"))

When I check the calculations on the source sheet using AutoFilter, the
numbers don't agree. When I remove the second condition, -('Enroll
I'!$F$2:$F$2741Q1)
It works and the Autofilter comes up with the same number as the
calculation. Q1 is a date, so I am asking for those later than this date.

Is the Autofilter the problem or the formula?

Thanks.

If you have TEXT entries in the range F2:F2741 they will evaluate to be
greater than the date entered in Q1.

See if this makes a difference:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--(ISNUMBER('Enroll
I'!$F$2:$F$2741)),--('Enroll I'!$F$2:$F$2741Q1),--('Enroll
I'!$N$2:$N$2741="No Planned Data"))

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using the formula below and it seems to work fine.

As an array:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--('Enroll
I'!$F$2:$F$2741Q1),--('Enroll I'!$N$2:$N$2741="No Planned Data"))

When I check the calculations on the source sheet using AutoFilter, the
numbers don't agree. When I remove the second condition, -('Enroll
I'!$F$2:$F$2741Q1)
It works and the Autofilter comes up with the same number as the
calculation. Q1 is a date, so I am asking for those later than this date.

Is the Autofilter the problem or the formula?

Thanks.

Your formula format looks correct. Is Autofilter returning more results, or
less? Check to make sure that when you filter the F column, you exclude the
date in Q1 (difference between = and alone)

Beyond that, we'd need more info about what your data looks like exactly. As
a way of error checking, perhaps break your sumproduct down into 100 rows,
and compare results. See if there's some set of cells that is throwing things
off.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PAL" wrote:

I am using the formula below and it seems to work fine.

As an array:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--('Enroll
I'!$F$2:$F$2741Q1),--('Enroll I'!$N$2:$N$2741="No Planned Data"))

When I check the calculations on the source sheet using AutoFilter, the
numbers don't agree. When I remove the second condition, -('Enroll
I'!$F$2:$F$2741Q1)
It works and the Autofilter comes up with the same number as the
calculation. Q1 is a date, so I am asking for those later than this date.

Is the Autofilter the problem or the formula?

Thanks.

I copied your formula:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--(ISNUMBER('Enroll
I'!$F$2:$F$2741)),--('Enroll I'!$F$2:$F$2741Q1),--('Enroll
I'!$N$2:$N$2741="No Planned Data"))

And got the #REF error. I used ISNUMBER to check F. For example
ISNUMBER(F2) is blank so it returned false.

My thought was that with your change it would have removed the falses.

Thanks.

"T. Valko" wrote:

If you have TEXT entries in the range F2:F2741 they will evaluate to be
greater than the date entered in Q1.

See if this makes a difference:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--(ISNUMBER('Enroll
I'!$F$2:$F$2741)),--('Enroll I'!$F$2:$F$2741Q1),--('Enroll
I'!$N$2:$N$2741="No Planned Data"))

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using the formula below and it seems to work fine.

As an array:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--('Enroll
I'!$F$2:$F$2741Q1),--('Enroll I'!$N$2:$N$2741="No Planned Data"))

When I check the calculations on the source sheet using AutoFilter, the
numbers don't agree. When I remove the second condition, -('Enroll
I'!$F$2:$F$2741Q1)
It works and the Autofilter comes up with the same number as the
calculation. Q1 is a date, so I am asking for those later than this date.

Is the Autofilter the problem or the formula?

Thanks.

As an array this returns "1"

=SUMPRODUCT(ISNUMBER('Enroll I'!$F$2:$F$2741))--('Enroll
I'!$F$2:$F$2741K1).

Autofilter returns 772 regardless of whether I type a value in autofilter or
select from list.

K1 and the autofilter = 12/31/2006

"T. Valko" wrote:

If you have TEXT entries in the range F2:F2741 they will evaluate to be
greater than the date entered in Q1.

See if this makes a difference:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--(ISNUMBER('Enroll
I'!$F$2:$F$2741)),--('Enroll I'!$F$2:$F$2741Q1),--('Enroll
I'!$N$2:$N$2741="No Planned Data"))

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using the formula below and it seems to work fine.

As an array:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--('Enroll
I'!$F$2:$F$2741Q1),--('Enroll I'!$N$2:$N$2741="No Planned Data"))

When I check the calculations on the source sheet using AutoFilter, the
numbers don't agree. When I remove the second condition, -('Enroll
I'!$F$2:$F$2741Q1)
It works and the Autofilter comes up with the same number as the
calculation. Q1 is a date, so I am asking for those later than this date.

Is the Autofilter the problem or the formula?

Thanks.