CountIfs to Sumproduct for Closed workbooks

[chris186h]

Hello all, im struggling with this formula and im unsure how to use sumproducts.
I need it to count the number of matches in a closed workbook.
Close workbook Ranges. Equipment'!$BC:$BC Containes words that should match with M11 in the open workbook. Equipment'!$BD:$BD contains dates which should match O9 in the open workbook.

=COUNTIFS('C:\Site\Site\[Data.xlsm]Equipment'!$BC:$BC,M11,'C:\Site\Site\[Data.xlsm]Equipment'!$BD:$BD,"="&O9)

I want a count of how both match
Many thanks in advanced

 

[RoryA]

A literal translation would be this:

=SUMPRODUCT(('C:\Site\Site\[Data.xlsm]Equipment'!$BC:$BC=M11)*('C:\Site\Site\[Data.xlsm]Equipment'!$BD:$BD=O9))

but you should really try to avoid entire columns when using this sort of SUMPRODUCT contruction, so perhaps:

=SUMPRODUCT(('C:\Site\Site\[Data.xlsm]Equipment'!$BC$1:$BC$10000=M11)*('C:\Site\Site\[Data.xlsm]Equipment'!$BD$1:$BD$10000=O9))

You can adjust the number of rows in the blue sections, but they should both be the same.

 

[chris186h]

A literal translation would be this:

=SUMPRODUCT(('C:\Site\Site\[Data.xlsm]Equipment'!$BC:$BC=M11)*('C:\Site\Site\[Data.xlsm]Equipment'!$BD:$BD=O9))

but you should really try to avoid entire columns when using this sort of SUMPRODUCT contruction, so perhaps:

=SUMPRODUCT(('C:\Site\Site\[Data.xlsm]Equipment'!$BC$1:$BC$10000=M11)*('C:\Site\Site\[Data.xlsm]Equipment'!$BD$1:$BD$10000=O9))

You can adjust the number of rows in the blue sections, but they should both be the same.

That worked perfectly. Thankyou RoryA.