Having problem with Subtotal in an array function

[gassarma]

=subtotal(6,IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))) and pressing ctrl+shift+enter.


The problem is whenever the if condition results in a FALSE, the array stores a FALSE and the product is resulting in #Value. Can anyone help?


The reason i am using subtotal is to provide the users flexibility to compute sum/product/ average of the results.


I had tried using Indirect function as follows. but it is not working.


="="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*( 'Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))" and confirming using ctrl+shft+enter. Cell J1 has a dropdown with options product, sum & average.


Please help

 

[NARAYANK991]

Hi ,


The IF statement does not have a execution path for the FALSE condition ; suppose after the 'Sheet1'!M$2:M$3761 you have a 0 as the result to be used if the condition is false ? In this case , the SUBTOTAL will also return 0.


Narayan