When my team tells me that their formula gives the wrong answer, I tell them
that they wrote their formula wrong, but this one...

I have a sumproduct formula that uses named ranges:
=SUMPRODUCT((hCountry=$B9)*(hIndustry=L$8)*hFees)

This formula returns zero, but the exact formula copied one cell to the left
returns the value 10. In fact, my sumproduct formulas from Columns A to K
calculate correctly - those to the right of column K return zero, and there
is nothing wrong with the formula (really...). It's as if Excel's memory can
only handle a certain number of cells

Any ideas?

thanks
Daniel

is this an array formula??

once the function has been entered you must press
Ctrl shift enter for the formula to work...

this is the first thing I thought of!!


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=542739

It is true that there are limits to the number of array formulae.

Test by deleteing columns a to k and see if column L starts working

If so you may need to move some formulae to a different sheet.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Daniel Bonallack" wrote in
message ...
When my team tells me that their formula gives the wrong answer, I tell
them
that they wrote their formula wrong, but this one...

I have a sumproduct formula that uses named ranges:
=SUMPRODUCT((hCountry=$B9)*(hIndustry=L$8)*hFees)

This formula returns zero, but the exact formula copied one cell to the
left
returns the value 10. In fact, my sumproduct formulas from Columns A to K
calculate correctly - those to the right of column K return zero, and
there
is nothing wrong with the formula (really...). It's as if Excel's memory
can
only handle a certain number of cells

Any ideas?

thanks
Daniel