Sumproduct Error

[RBenny]

Dear experts,

Having gone around for an hour, I can't figure out why my Sumproduct doesn't work.

=SUMPRODUCT(--('Balance Sheet_Property SS'!$C$14:$C$271=$S$32),--('Balance Sheet_Property SS'!$D$14:$D$271=$S$30),--('Balance Sheet_Property SS'!$F$14:$F$271=$S$33),--('Balance Sheet_Property SS'!$NU$3:$SC$3=$S$31)*'Balance Sheet_Property SS'!$NU$14:$SC$271)

No text, no formula errors in ranges. Not sure what else to do. Any ideas?

Thanks

 

[FormR]

Hi, is there only one cell in 'Balance Sheet_Property SS'!$NU$3:$SC$3 that matches cell $S$31 or are there many?

 

[BlueHornet]

Try this format instead: SUMPRODUCT((( 'Balance Sheet_Property SS'!$C$14:$C$271) = $S$32) * ( ... etc.

That is, set off the ranges you want to check against a single cell into their own sets of parentheses, and don't include the single value in that set of "range" parentheses.

I also prefer the "*" operator between terms, instead of the "--" operator that you've used, but I suppose those operators work equally well.

Just noticed: All of the terms in the SUMPRODUCT have to be over similarly-sized ranges. You had at least one term that didn't extend over the same-sized range as C14:C271. I don't think that will ever work inside the SUMPRODUCT ... though you could have it as a term outside the SUMPRODUCT range, of course.

 

[RBenny]

There are many. It's a row of YEAR and it should result in 12 for any selected year.

 

[RoryA]

As per BlueHornet, you need to multiply as your ranges aren't the same orientation:

=SUMPRODUCT(('Balance Sheet_Property SS'!$C$14:$C$271=$S$32)*('Balance Sheet_Property SS'!$D$14:$D$271=$S$30)*('Balance Sheet_Property SS'!$F$14:$F$271=$S$33)*('Balance Sheet_Property SS'!$NU$3:$SC$3=$S$31)*'Balance Sheet_Property SS'!$NU$14:$SC$271)

 

[RBenny]

Try this format instead: SUMPRODUCT((( 'Balance Sheet_Property SS'!$C$14:$C$271) = $S$32) * ( ... etc.

That is, set off the ranges you want to check against a single cell into their own sets of parentheses, and don't include the single value in that set of "range" parentheses.

I also prefer the "*" operator between terms, instead of the "--" operator that you've used, but I suppose those operators work equally well.

Just noticed: All of the terms in the SUMPRODUCT have to be over similarly-sized ranges. You had at least one term that didn't extend over the same-sized range as C14:C271. I don't think that will ever work inside the SUMPRODUCT ... though you could have it as a term outside the SUMPRODUCT range, of course.

Blue Hornet - Lifesaver. This worked! I was using "--" to use 0,1 results instead of True/False, but I guess that didn't matter.