Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and SUMPRODUCT
I have 3 worksheets.
The first worksheet contains a column that has text in it and is formatted so. The second worksheet, uses VLOOKUP to bring that value over. The third worksheet uses SUMPRODUCT. The formula worked prior to adding the parameter described above. The formula is: =SUMPRODUCT(--('Enroll I'!$O$2:$O$2237="Regular"),--('Enroll I'!$E$2:$E$2237<" Open"),--('Enroll I'!$M$2:$M$2237="Yes"),--('Enroll I'!$H$2:$H$22370),--('Enroll I'!$N$2:$N$2237="No Data")) This is the only new peice: ('Enroll I'!$O$2:$O$2237="Regular"). I get the #N/A error. I have tried changing the formating on the second worksheet but it is not flying. Ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and SUMPRODUCT
Possibly there's already a #N/A error in range ''Enroll I'!$O$2:$O$2237
?? "PAL" wrote in message ... I have 3 worksheets. The first worksheet contains a column that has text in it and is formatted so. The second worksheet, uses VLOOKUP to bring that value over. The third worksheet uses SUMPRODUCT. The formula worked prior to adding the parameter described above. The formula is: =SUMPRODUCT(--('Enroll I'!$O$2:$O$2237="Regular"),--('Enroll I'!$E$2:$E$2237<" Open"),--('Enroll I'!$M$2:$M$2237="Yes"),--('Enroll I'!$H$2:$H$22370),--('Enroll I'!$N$2:$N$2237="No Data")) This is the only new peice: ('Enroll I'!$O$2:$O$2237="Regular"). I get the #N/A error. I have tried changing the formating on the second worksheet but it is not flying. Ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and SUMPRODUCT
Hi,
Check the VLOOKUP column and make sure none of the results are #N/A that will cause the entire SUMPRODUCT formula to return #N/A. You should be using (2003) something like: =IF(ISNA(VLOOKUP(A1,Table,3,False)),"",VLOOKUP(A1, Table,3,False)) to handle NA's -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PAL" wrote: I have 3 worksheets. The first worksheet contains a column that has text in it and is formatted so. The second worksheet, uses VLOOKUP to bring that value over. The third worksheet uses SUMPRODUCT. The formula worked prior to adding the parameter described above. The formula is: =SUMPRODUCT(--('Enroll I'!$O$2:$O$2237="Regular"),--('Enroll I'!$E$2:$E$2237<" Open"),--('Enroll I'!$M$2:$M$2237="Yes"),--('Enroll I'!$H$2:$H$22370),--('Enroll I'!$N$2:$N$2237="No Data")) This is the only new peice: ('Enroll I'!$O$2:$O$2237="Regular"). I get the #N/A error. I have tried changing the formating on the second worksheet but it is not flying. Ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and SUMPRODUCT
This is the only new peice: ('Enroll I'!$O$2:$O$2237="Regular"). I get the
#N/A error. Replace a new peice with this one: (ISNUMBER(SEARCH("Regular",'Enroll I'!$O$2:$O$2237))) "PAL" wrote: I have 3 worksheets. The first worksheet contains a column that has text in it and is formatted so. The second worksheet, uses VLOOKUP to bring that value over. The third worksheet uses SUMPRODUCT. The formula worked prior to adding the parameter described above. The formula is: =SUMPRODUCT(--('Enroll I'!$O$2:$O$2237="Regular"),--('Enroll I'!$E$2:$E$2237<" Open"),--('Enroll I'!$M$2:$M$2237="Yes"),--('Enroll I'!$H$2:$H$22370),--('Enroll I'!$N$2:$N$2237="No Data")) This is the only new peice: ('Enroll I'!$O$2:$O$2237="Regular"). I get the #N/A error. I have tried changing the formating on the second worksheet but it is not flying. Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUMPRODUCT with a VLOOKUP | Excel Worksheet Functions | |||
VLOOKUP, IF, SUMPRODUCT...oh my! | Excel Worksheet Functions | |||
SUMPRODUCT or VLOOKUP | Excel Worksheet Functions | |||
do i use vlookup or sumproduct??? | Excel Discussion (Misc queries) | |||
Sumproduct and Vlookup | Excel Discussion (Misc queries) |