SUMPRODUCT formula only works on half my columns

[Mike Guest98]

Hi


I have spent many hours trying to figure out what the following problem is and how to fix it:



- Using Excel 2007
- Using a =SUMPRODUCT formula (long one) that I copied from another worksheet to Word and saved, then copied to Excel. The problem isn’t with the formula because it works but on only half of the columns and ranges in the formula.
- I have used the error checking, Trace precedents, Trace Dependents says “found no formula that refer to the active cell.
- I looked at conditional formatting but none.
- All the cells are formatted as text and none have any formula in them but only half work. Now I do have a =ISTEXT formula watching for text when placed in a cell. Is that the issue?





Any thoughts would be appreciated.

<style type="text/css">p { margin-bottom: 0.1in; line-height: 115%; }</style>

 

[Special-K99]

What's the SUMPRODUCT formula?
What does "it only works on half my columns" mean? It doesnt give you the results you expect by looking at the spreadsheet?

 

[Mike Guest98]

I've used this formula on another sheet and it works on every column and range listed in the formula. In this case of the 8 columns in the formula only 4 are working (to test I put data in a column and see if the info. is seen in formula cell. The thing is its random which column work and which do, no pattern to it. I have not listed the formula because it's long and complicated. Something is blocking certain cells from being seen by the formula!!!

 

[Special-K99]

Well it's clearly not random thats not how it works.
If it works on another sheet then it's your data thats causing the problems.

 

[Mike Guest98]

Thanks for your reply. I solved it and it was a data problem in some of the cells.