| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMProduct Part II
I am working with this formula and it works great.
=SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
try
=SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) "PAL" wrote: I am working with this formula and it works great. =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes"),--('Enroll I'!P$2:$P$2921="Regular")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
opps something was wrong try
just found an extra ) in the last yes statement =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes"),--('Enroll I'!P$2:$P$2921="Regular")) "Eduardo" wrote: try =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) "PAL" wrote: I am working with this formula and it works great. =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes"),--('Enroll I'!P$2:$P$2921="Regular")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
You have to put the extra condition before the overall closing parenthesis: Code: -------------------- =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes"),--('Enroll I'!P$2:$P$2921="Regular")) -------------------- -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122392 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
When I add an additional parameter in
--('Enroll I'!P$2:$P$2921="Regular") I get the infamous, "#N/A" error. Are there any #N/A errors already in that range? If so, you should correct those formulas so they don't return #N/A. -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am working with this formula and it works great. =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
=SUMPRODUCT(
--('Enroll I'!A$2:$A$2921=$B17), --('Enroll I'!$O$2:$O$2921="NSITE0"), --('Enroll I'!M$2:$M$2921="Yes"), --('Enroll I'!N$2:$N$2921="Yes"), --('Enroll I'!P$2:$P$2921="Regular")) If this post helps click Yes --------------- Jacob Skaria "PAL" wrote: I am working with this formula and it works great. =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMProduct Part II
Hi,
Although your formula has a closing parenthesis in the wrong place that should not return an #N/A error message, just an incorrect result possibly. Check the range 'Enroll I'!P$2:$P$2921 there is probably a formula in that range returning and #N/A -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PAL" wrote: I am working with this formula and it works great. =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")) When I add an additional parameter in --('Enroll I'!P$2:$P$2921="Regular") To look like this.... =SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B17),--('Enroll I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),--('Enroll I'!N$2:$N$2921="Yes")),--('Enroll I'!P$2:$P$2921="Regular")) I get the infamous, "#N/A" error. The page I am referencing, 'Enroll I", gets the value from another sheet. It seems to be in the right format -I have tried, General or Text. I have also tried w and w/o the array symbols. Ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct using part of a Cell | Excel Discussion (Misc queries) | |||
| Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
| Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
| Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
| sumproduct of part cells of a range with blanks | Excel Discussion (Misc queries) | |||
Hybrid Mode
