sumproduct text with indirect

[matrix_xrs]

hi everyone,

can somoene tell me why my formula is giving me a # value error

in collumn M9 to M16 contains text and blank cells and i can't see what is wrong with my formula.

SUMPRODUCT(--(INDIRECT("'"&$L16&"'!$i$8:$m$8")=$M$15),--(INDIRECT("'"&L16&"'!$i$9:$i$16")=$A16),INDIRECT("'"&$L16&"'!$M$9:$m$16"))

am i missing something

thanks for everyone help

[Jonmo1]

If we could first remove the Indirect (for troubleshooting purposes).

Can you post what the formula should be without indirect?


My first impression is that Sumproduct with the --(),--() syntax can't use ranges of different dimensions
Like I8:M8 and I9:I16

[matrix_xrs]

hi,

this is how it would be without the sumproduct

SUMPRODUCT(--('10080-00'!I8:M8=$M$15),--('10080-00'!I9:I17=$A16),('10080-00'!M9:M17))

[matrix_xrs]

hi,

your correct after looking at the fomula i didn't require the first array the formula works if i do this

SUMPRODUCT(--('10080-00'!I9:I17=$A16),('10080-00'!M9:M17))

[Jonmo1]

In that case it's just

=SUMIF('10080-00'!I9:I17,$A16,'10080-00'!M9:M17)

[martindwilson]

fyi --('10080-00'!I8:M8=$M$15) i8 to m8 is not the same size as I9:I17
and i think you need transpose if the range is not in the same direction
=SUMPRODUCT(--(TRANSPOSE($I$8:$P$8)=$M$15),--($I$9:$I$16=$A16),$M$9:$M$16) which needs array entering