dcd,

One thing that immediately jumps out is that your ranges aren't balanced --- one is a row, one is a
column, and one is a block! SUMPRODUCT requires ranges of equal size, and of only 1 dimenstion (Row
or column).

What it looks like you are trying to do would actually require a row of formulas, along the lines
of:

=IF(ISNUMBER(SEARCH(" - D",'Discussed vs Opened'!E1)),
SUMPRODUCT(('Discussed vs Opened'!$C2:$C1512=Summary!A3)*
('Discussed vs Opened'!E2:E1512="Yes")),0)

copied to the right for 41 columns (or until E2:E1512 becomes AS2:AS1512)

and then sum those formulas...

If that isn't the case, then it would be better fopr you to explain what it is that you are actually
trying to do....

HTH,
Bernie
MS Excel MVP


"dcd123" wrote in message
...

I am running SUMPRODUCT with 3 criteria, 2 are on another sheet and one
of those two is searching for a portion of the cell content. I am
getting #N/A as the result. Can anyone tell me why?

Here is my formula:

=SUMPRODUCT(ISNUMBER(SEARCH(" - D",'Discussed vs
Opened'!A1:AS1))*('Discussed vs
Opened'!C2:C1512=Summary!A3)*('Discussed vs Opened'!E2:AS1512="Yes"))

The first criteria is the one that contains only a portion of the cell
content and I'm wondering if the "-" is throwing it off. The criteria
reference in the second is a person's name (Summary!A3).

I hope this makes sense and that someone can help!!

Thanks!


--
dcd123
------------------------------------------------------------------------
dcd123's Profile: http://www.excelforum.com/member.php...o&userid=25396
View this thread: http://www.excelforum.com/showthread...hreadid=473799