=SUMPRODUCT(--(INDIRECT

[Rodrigues]

All
I have the following formula and works fine:
=SUM(IF(FREQUENCY('[Prod Log Days 060715.xls]A - M'!$A$2:$A$10000,'[Prod Log Days 060715.xls]A - M'!$A$2:$A$10000)>0,1))

Now I need to add Indirect function/, but doesn't work:

=SUMPRODUCT(IF(FREQUENCY(INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000, (INDIRECT("'" & BasePath & '[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000)>0,1))

Could anyone tell me where is wrong or any other alternative?
Many thanks
Regards
Rodrigues

 

[NARAYANK991]

Hi Rodrigues ,

Try either of the following :

=SUM(IF(FREQUENCY(INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000"), INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000"))>0,1))

entered as an array formula , using CTRL SHIFT ENTER.

or

=SUMPRODUCT(IF(FREQUENCY(INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000"), INDIRECT("'" & BasePath & "[Prod Log Days " & CurrentDate & ".xls]A - M'!$A$2:$A$10000"))>0,1))

entered as a normal formula.

Narayan

 

[Rodrigues]

Hi Narayan
You are a star!
Thanks again.
Rodrigues