How to view details in SumProduct

[Magee]

Hi All,

I have a list of data and a sum product formula that looks at certian criteria based on a value entered into cell A12

=SUMPRODUCT(--('New Stock (Inc Pipe, PI, CUSTo)'!$B$2:$B$100>0),--('New Stock (Inc Pipe, PI, CUSTo)'!$M$2:$M$100="No"),--('New Stock (Inc Pipe, PI, CUSTo)'!$AI$2:$AI$100<>"Vauxhall VSC Stock"),--(ISNUMBER(SEARCH(A12,'New Stock (Inc Pipe, PI, CUSTo)'!$E$2:$E$100))))

This returns the number of items the meet this criteria.

Is there a way I can double click the cell (or something else) and then it will display the details of the result?

 

[JamesW]

Hi there,

You can maybe try Evaluate Formula which gives you details on what the formula is doing step by step with results.

Alt + T + U + F

 

[Richard Schollar]

Alternatively, you could highlight each term in the formula bar and hit F9 to return an array constant of the results of the term (note that you only have 1024 characters in the formula bar to display the results and with larger ranges you will into this limit very quickly).

DonkeyOte (MrExcel MVP) built an xla to analyse the results - details of this can be found here:

http://www.mrexcel.com/forum/showthread.php?t=350657&highlight=sumproduct

I am afraid I don't know if the file is still available at the link provided (I can't download anything as at work) but general consensus was the tool was very good.

 

[Magee]

thanks both will give it a go.