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
 

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

Average IF 3
SUMProduct Part II 5
VLOOKUP and SUMPRODUCT 1
SUMPRODUCT 3
Scrabble Value calculation for Welsh words 0
Many ifs.... 1
Complex if 9
Need to add to a SUMPRODUCT formula 2

Back
Top