SUMPRODUCT
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.
|