SUMPRODUCT * versus ,

I've seen SUMPRODUCT formulas written with either a comma or an asterisk (times) between arrays. Excel Help only mentions commas. When would you use one or the other OR do they work interchangeably? Thanks

ChemistB

Originally Posted by ChemistB

I've seen SUMPRODUCT formulas written with either a comma or an asterisk (times) between arrays. Excel Help only mentions commas. When would you use one or the other OR do they work interchangeably? Thanks

ChemistB

There is a subtle difference. Using the '*' will return an ERROR if the ranges include a text value in a column of numbers, which usually occurs when a table field heading is included above a column of numbers. Using the ',' along with a '--' double unary operator gets round this problem.

For an excellent and in my opinion the definitive discussion on SUMPRODUCT, you should look at the following URL - and bookmark it. I've lost count of the number of times I've referred to it.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


HTH

Thanks NBVC! That pretty much explains everything.

ChemistB