SUMPRODUCT

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

T. Valko

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"))
 
P

PAL

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

PAL

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional 1
Average IF 3
SUMProduct Part II 6
VLOOKUP and SUMPRODUCT 1
SUMPRODUCT 3
Many ifs.... 1
Complex if 9
Calculation with EDATE Help Needed! 2

Top