SUMPRODUCT

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

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$2741>Q1),--('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$2741>Q1)
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$2741>Q1),--('Enroll
I'!$N$2:$N$2741="No Planned Data"))
 
I copied your formula:

=SUMPRODUCT(--('Enroll I'!$O$2:$O$2741="Regular"),--(ISNUMBER('Enroll
I'!$F$2:$F$2741)),--('Enroll I'!$F$2:$F$2741>Q1),--('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.
 
As an array this returns "1"

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

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

K1 and the autofilter = 12/31/2006
 
Back
Top