sumproduct problems

[Pandab2002]

I have a spreadsheet that looks at a column in another spreadsheet and counts the instance of certain criteria. I use the following:

=COUNTIF('[open.xls]Service'!$K$2:$K$60, 1)

This works great. Now I want to apply the same logic usint the =SUMPRODUCT function to only count criteria in column K if there is a 1 in column L. I have exhausted my efforts to find the correct entry. I keep getting errors. I referred to faq68-4725 and tried to adjust the data, but still no luck. Any suggestions?

Here's an example of what I tried.
=SUMPRODUCT(('[open.xls]MAC Service'!$K$2:$K$200,1)*'[open.xls]MAC Service'!$L$2:$L$200,1))

 

[bluedragon2]

=SUMPRODUCT(('[open.xls]MAC Service'!$K$2:$K$200)*('[open.xls]MAC Service'!$L$2:$L$200=1))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Pandab2002]

THANK YOU BLUE. I thought I tried every possible combination.

 

[bluedragon2]

This is a little overkill of the formula because you still only have one criteria (a 1 in col L). You could still use the COUNTIF for that. But, If you had two criteria (Col K=2 and Col L=1) then the SUMPRODUCT formula would work:

=SUMPRODUCT((K2:K200=2)*(L2:L200=1))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...