Multiple Criteria in SumProduct, N/A Result

[dcd123]

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

Here is my formula:

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

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

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

Thanks

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

=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

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]

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

 

[Zack Barresse]

No, not with your current structure.

 

[JE McGimpsey]

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]>,

 

[Harlan Grove]

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]

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!!!!