Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct help | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT? | Excel Worksheet Functions |