Multiple Embedded SUMPRODUCT Error

[RBenny]

Hello friendly people,

I've racked my brain on a formula with no luck. I've read a few other posts focused on COUNTIF and tried to change my results to 1 & 0 by using "--". Still, I get a #VALUE or #N/A error.

I would like to make a sum of data in a large array based on multiple criteria. I have 3 columns that make up my criteria, then a row that makes up another set of criteria and then the array of data to evaluate based on the results of my column and row checks (should result in 1 or 0).

[TABLE="class: grid, width: 0"]
<tbody>[TR]
[TD]Row 12 (Header Names)[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column F[/TD]
[TD]Columns IR: Column MH[/TD]
[TD]Columns IR: Column MH[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Criteria 1 (Text)[/TD]
[TD]Criteria 2 (Text)[/TD]
[TD]Criteria 3 (Text)[/TD]
[TD]Criteria 4 (Text) [/TD]
[TD]Sum array (Rows 13:269)[/TD]
[/TR]
</tbody>[/TABLE]

I've tried two iterations, where Columns R & S I've listed the test criteria in cells.

1. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*(('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$19)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$20)*1),('Balance Sheet_Property SS'!$IR$13:$MH$269))

This produces a #VALUE result.

2. =SUMPRODUCT(--('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*--('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*--('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*--('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18:$S$20),('Balance Sheet_Property SS'!$IR$13:$MH$269))

This produces an #N/A result.

Any help appreciated. Thanks.

 

[steve the fish]

Whats the aim of this bit?

(('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$19)*1+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$20)*1)

Is that meant to be an 'or'?

 

[RBenny]

Hi Steve, Yes.

 

[steve the fish]

Try this:

=SUMPRODUCT(('Balance Sheet_Property SS'!$D$13:$D$269=$R$18)*('Balance Sheet_Property SS'!$C$13:$C$269=$R$19)*('Balance Sheet_Property SS'!$F$13:$F$269=$R$20)*(('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$18)+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$19)+('Balance Sheet_Property SS'!$IR$12:$MH$12=$S$20)>0)*'Balance Sheet_Property SS'!$IR$13:$MH$269)

 

[RBenny]

Hi Steve,

No change to the outcome. Is there anything else I can provide to support investigation?

 

[XOR LX]

Hi,

Your first formula is syntactically sound. Are there any errors in any of the cells being referenced within that formula?

You can use CTRL+G and then Special/Formulas/Errors if you weren't aware.

Regards

 

[RBenny]

Hi XOR,

I've just checked, there are no errors within the range.

Thanks

 

[steve the fish]

I'll presume if no errors then there is text that can't be coerced into number somewhere within the sum range.

 

[XOR LX]

In the first formula posted there is no requirement for text to be coerced to number. The arguments are all Boolean expressions apart from the final one, which is not being coerced.

Without seeing an actual workbook, I'm not sure what else I can suggest that might be causing the error: the first formula looks perfectly good to me.

Regards