Convert Countifs to Sumproduct

[spydey]

I have a formula in the destination workbook using countifs, it works well .... as long as the source workbook is open, otherwise it fails. I know that countifs doesn't work across closed workbooks.

So I want to convert it to sumproduct, which I know works across closed workbooks. I am having an issue converting it to work as expected.

I think that just an extra set of eyes will help clear any issues up.

Here is my countifs formula, which is being confirmed with CSE.

=IF(COUNTIFS('SourceWorkbook.xlsx'!Person,$E2,'SourceWorkbook.xlsx'!Location,$E3,'SourceWorkbook.xlsx'!Type,{"*Basketball*","*BBall*"})=0,0,COUNTIFS('SourceWorkbook.xlsx'!Person,$E2,'SourceWorkbook.xlsx'!Location,$E3,'SourceWorkbook.xlsx'!Type,{"*Basketball*","*BBall*"},'SourceWorkbook.xlsx'!Days,">="&0,'SourceWorkbook.xlsx'!Days,"<="&30))

I have converted countifs to sumproduct in the past but I seem to be messing up on something.

This is what I have as my countifs formula from above converted to sumproduct. I am confirming with CSE.

=IF(SUMPRODUCT(('SourceWorkbook.xlsx'!Person=$E2)*('SourceWorkbook.xlsx'!Location=$E3)*('SourceWorkbook.xlsx'!Type={"*Basketball*","*BBall*"}))=0,0,SUMPRODUCT(('SourceWorkbook.xlsx'!Person=$E2)*('SourceWorkbook.xlsx'!Location=$E3)*('SourceWorkbook.xlsx'!Type={"*Basketball*","*BBall*"})*('SourceWorkbook.xlsx'!Days>=0)*('SourceWorkbook.xlsx'!Days<=30)))

For some reason, I can't get it to work correctly. I don't get the expected output.

Any ideas or thoughts? Did I miss something?

-Spydey

 

[spydey]

So after a bit more research, I think that my issue lays in the fact that I am using a wildcard in one of my parameters, eg *Basketball* .... is there anyway around this?

-Spydey

 

[spydey]

*BUMP

Any way I can convert from a countifs using wild cards (*), to a sumproduct?

My main two issues are:

1.) Countifs allows me to use wildcards when referencing text, but I cannot use it across workbooks if the source workbook is closed.
2.) Sumproduct allows me to work across workbooks, even if the source workbook is closed, but as I understand it, I cannot use wild cards (*) while using sumproduct.

Any thoughts or ideas on how I can get around this, besides just using countifs and having the source workbook open?

Cheers!

-Spydey