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:

	Demand £ (LW)	Demand LW -1 £	Demand LW -2 £	Demand LW -3 £
BLACK	#N/A
BLEACH WASH
BLUE WASH
GREY WASH
INDIGO WASH
COLOUR
WHITE

<tbody>
</tbody>

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.