Counting cells based on multiple criteria "between two dates and one other criteria" in closed workbook.

[Lostchrono]

Hello,

First time posting on MrExcel. You guys have been great by helping me just by Google searching but I am hoping that you can help me with a formula.

I am trying to count the criteria "Any cell that equals 10 in column K" between two dates in column A in a closed workbook. In the sheet I am using to count the closed workbook I am referencing the dates through cell CD3(12/28/2014) and CD4(1/3/2014).

=SUMPRODUCT(--('[Workbook2014.xlsx]2014'!$A:$A>=CD3),--('[Workbook2014.xlsx]2014'!$A:$A<=CD4),--('[SADIE2014.xlsx]2014'!$K:$K=10))

Before I used =SUMPRODUCT(--('[Workbook2014.xlsx]2014'!$K$2:$K$64=10)) but I really want to make this a global solution so I can reproduce it over several rows down and just have to change the column K value and date refrences.

Thank you in advance for all your help.

 

[Lostchrono]

I accidentally referenced CD3 as 12/28/2014 instead of 12/28/2013. The code still looks the same however.

[COLOR=#333333]=SUMPRODUCT(--('[Workbook2014.xlsx]2014'!$A:$A>=CD3),--('[Workbook2014.xlsx]2014'!$A:$A<=CD4),--('[SADIE2014.xlsx]2014'!$K:$K=10))[/COLOR]

I am able to receive a value other than 0 now but it is slightly off from the original.