Sumproduct troubleshoot - advice welcome

[horkstar12]

Hello,

Looking to implement a sumproduct across 2 variables and i cant get my head around why its returning a #NA, can anyone help?

I have colour in column and Demand across the top as below:


[TABLE="width: 454"]
<tbody>[TR]
[TD][/TD]
[TD]Demand £ (LW)[/TD]
[TD]Demand LW -1 £[/TD]
[TD]Demand LW -2 £[/TD]
[TD]Demand LW -3 £[/TD]
[/TR]
[TR]
[TD]BLACK[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLEACH WASH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUE WASH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GREY WASH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INDIGO WASH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COLOUR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WHITE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula returning a #NA is:

=SUMPRODUCT(('by cat col'!N2:N5833=Sheet1!D2)*('by cat col'!O1:R1=Sheet1!E1),'by cat col'!O2:R5833)

In the formula N2:N5833 is colour, O1:R1 is the Demand £, Demand LW -1....

Tried to adjust the formatting so all fields noted as Text are so and same for number fields.

Any help please?

 

[bhos123]

In sumproduct formula both the ranges should have equal Ranges.

 

[horkstar12]

In sumproduct formula both the ranges should have equal Ranges.

Hi,

Thanks for the reply, when you say ranges, my 2 columns ranges match down to 5833, what other figures need to match as the row column goes across as required.

Thanks in advance, appreciate the help.

 

[bhos123]

= sumproduct(Range1,Range2)

your range2 has 4*5831 cells, I am not sure what your Range1 has.