ExcelBanter - SUMProduct Part II

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUMProduct Part II (https://www.excelbanter.com/excel-worksheet-functions/238892-sumproduct-part-ii.html)

PAL

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?

Eduardo

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?

Eduardo

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?

T. Valko

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?

Jacob Skaria

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?

NBVC[_124_]

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

Shane Devenshire[_2_]

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?

All times are GMT +1. The time now is 11:16 PM.