Excel Countifs Formula

[hrayani]

Hello Friends,

I am using the below formula in my sheet. The problem is that it does not work on a closed workbook.
The source workbook needs to be open in order for the formula to work.

=COUNTIFS('DATA ENTRY - INLINE.xlsm'!gsm_pc_weight_ref_no,B6,'DATA ENTRY - INLINE.xlsm'!gsm_pc_weight_date,">="&K16,'DATA ENTRY - INLINE.xlsm'!gsm_pc_weight_date,"<="&P16,'DATA ENTRY - INLINE.xlsm'!gsm_pc_weight_category,"GSM")


Is there any other formula which can replace the current formula - which works on a closed workbook.


Regards,

Humayun

 

[Danmc]

nothing works on a closed workbook, you will have to open it to run your formulas

 

[Marcelo Branco]

SUMPRODUCT works with closed files.

Maybe something like this
=SUMPRODUCT(--('file name'!Range1=B6),--('file name'!Range2>=K16),--('file name'!Range2<=P16),--('file name'!Range3="GSM"))

Range1, Range2 and Range3 must have the same size

M.

 

[Marcelo Branco]

See
https://www.youtube.com/watch?v=H40-xBVtl-Q

M.

 

[hrayani]

Hi Marcelo,

Thanks for the reply....

The formula you provided is working PERFECT with a closed workbook.

Thanks

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[Danmc]

thanks marcelo, didn't know about sumproduct. great information

 

[Marcelo Branco]

thanks marcelo, didn't know about sumproduct. great information

You are welcome.

M.