Using SUMPRODUCT to count values in closed workbook

Hi there,

I'm testing out an application using SUMPRODUCT in worksheetfunction in VBA.(reason i'm using this formula is because the lookup range increases on-going)

What I would like to have Excel do is to give me a total count of "Yes" in a specific site selected,and output that count in cell F2 in the main opened workbook.

The code I have so far is:

Please Login or Register  to view this content.

The lookup columns in data book are: These are in a closed workbook, in sheet "C"

Status Site
Yes c
Yes c
Yes c
No c
No c
Yes c
No c
Yes c
Yes c
No c


in the same closed workbook, in worksheet "B"

Status Site
Yes b
Yes b
Yes b
No b
No b
Yes b
No b
Yes b
Yes b
No b

Thank you for any input or guidance

Hello,

Is anybody able to help with this request?

Thanks

This link should be helpful for you.

http://xldynamic.com/source/xld.SUMPRODUCT.html#vba


In short, though, your problem was that you used

Please Login or Register  to view this content.

and it should be

Please Login or Register  to view this content.