Sumproduct over the whole column does not work

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
ABCDEFG
2ApplesRed10Conditions
3PearsYellow20FruitApples
4ApplesGreen55ColourRed
5
685Total10
7
8Total#NUM!<<< why doesnt this work?
Sheet2


Many thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top