Multiple Criteria in SumProduct, N/A Result

**dcd123** · 10-06-2005, 11:35 AM

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!

Bernie Deitrick · 10-06-2005, 01:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Bernie Deitrick · 10-06-2005, 02:05 PM

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

would work better for copying....

Sorry about that.

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

**dcd123** · 10-06-2005, 02:29 PM

I need the cell references to remain as they are, row, column, and block. Is there another function that will accommodate this?

Zack Barresse · 10-06-2005, 04:05 PM

No, not with your current structure.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)

"dcd123" <[email protected]> wrote in
message news:[email protected]...
>
> I need the cell references to remain as they are, row, column, and
> block. Is there another function that will accommodate this?
>
>
> --
> dcd123
> ------------------------------------------------------------------------
> dcd123's Profile:
> http://www.excelforum.com/member.php...o&userid=25396
> View this thread: http://www.excelforum.com/showthread...hreadid=473799
>

JE McGimpsey · 10-06-2005, 05:05 PM

SUMPRODUCT()'s arguments aren't the problem. Since the ranges are
multiplied, they don't have to be balanced, since only the result of the
multiplication (in this case a rectangular range) is the argument to
SUMPRODUCT, not the individual ranges.

However, since the ranges are of different size (e.g, the Row, A1:AS1 is
4 columns wider than the block E2:AS1512), the multiplication of these
ranges will result in #N/A's filling the last 4 columns of the resulting
array, and therefore SUMPRODUCT() will also return #N/A.

This change works:

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

but I don't know what the OP intended with the extra columns...

The In article <[email protected]>,
"Bernie Deitrick" <deitbe @ consumer dot org> wrote:

> 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).

Harlan Grove · 10-06-2005, 05:05 PM

dcd123 wrote...
>I need the cell references to remain as they are, row, column, and
>block. Is there another function that will accommodate this?

Here's your formula.

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

On their own, none of the criteria present a problem. Also, pairing the
middle criteria with either of the other two presents no problem.
However, the pairing the first and third criteria NECESSARILY results
in an error because the two ranges have different numbers of multiple
columns. Excel can't handle A1:S1 * E2:AS1512. It doesn't make sense.
What would the extra columns in the former correspond to in the latter?

There may be a way to do what you want, but you're going to have to
explain it IN PROSE, not with formulas.

**dcd123** · 10-07-2005, 08:26 AM

Thanks, JE. You pointed out my lack of attention to detail in this formula. The extra columns were unnecessary and making that change made the difference!!!!