Sumproduct over the whole column does not work

[gaj104]

Hi,

Why is it if I use a defined range my sumproduct formula works, but when I use an entire column the sumproduct fails. Is there a workaround to this?

Book1
	A	B	C	D	E	F	G
2	Apples	Red	10		Conditions
3	Pears	Yellow	20		Fruit	Apples
4	Apples	Green	55		Colour	Red
5
6			85		Total	10
7
8					Total	#NUM!	<<< why doesnt this work?
Sheet2

Many thanks

 

[Richard Schollar]

Sumproduct won't work with entire columns, so the workaround is not to use entire columns (the max you can use is A2:A65536 for example).

 

[pgc01]

Hi gaj104

You cannot use whole columns in any array formula. There is no workaround (that I know of).

You can, however, specify a big numbers of rows

A1:A20000

Hope this helps
PGC