Coverting COUNTIFS to calculate with referenced file closed

[TMcG]

Thanks in advance for any help you can offer. The documents that I am working with are government classified, so I cannot provide samples.

I am successfully using multiple =COUNTIFS formulas, but they reference a large document that needs to be open in order for COUNTIFS to calculate. I need them to perform this operation with the referenced document closed. This document is very large and used by multiple users, so I need it to reference without being opened.

Here is my current working formula (sanitized):

=COUNTIFS('\\serverpath\[reference.xlsx]2011'!$E$E,A2,'\\serverpath\[reference.xlsx]2011'!$G$G,"I-*")

A2 contains an employee number
2011 is the reference worksheet name
Column E lists an employee number
Column G lists a particular citation level (in this case, level I)

I need the formula to return a value into cell G3 that tallies the number of times employee X has received citation I-*.

I can provide some additional detail if necessary, but really I just need to find a way to convert my COUNTIFS formula.

Thank you very much for your help!

 

[RichardMGreen]

Not entirely sure this will work, but you could try:-
=SUMPRODUCT(('\\serverpath\[reference.xlsx]2011'!$E$2:$E$65535=A2)*('\\serverpath\[reference.xlsx]2011'!$G$2:$G$65535)="I-"&"*"))

Hopefully, Excel will see this section "I-"&"*" as "I-" and anything else (using the * as a wildcard). I've assumed row 1 contains headers and you may want to adjust the last row of each part to suit your needs and the version of Excel you're using.

 

[T. Valko]

Not entirely sure this will work, but you could try:-
=SUMPRODUCT(('\\serverpath\[reference.xlsx]2011'!$E$2:$E$65535=A2)*('\\serverpath\[reference.xlsx]2011'!$G$2:$G$65535)="I-"&"*"))

Hopefully, Excel will see this section "I-"&"*" as "I-" and anything else (using the * as a wildcard). I've assumed row 1 contains headers and you may want to adjust the last row of each part to suit your needs and the version of Excel you're using.

SUMPRODUCT doesn't support wildcards.

 

[T. Valko]

Biff- When I run your formula, it executes and takes about 10 seconds or so to think before returning zero.

The file that I am referencing currently has 51619 entries.

Well, that's how long it takes that formula to calculate on 50k + rows of data. That's why I suggested to NOT use entire columns as range references.

Thank you so much for your quick response.

My COUNTIFS formula returns the correct results.

A2 (and column E in the reference file) contain employee numbers up to 6 digits in length with leading zeros. A2 and column E are both formatted properly to be numbers of six digits.

Column G displays text always beginning with I-, II-, III-, IV-, or V-.

Please let me know what else I can provide. Your help is greatly appreciated.

That section highlighted in red is probably why none of these formulas work. As Barry noted, COUNTIF(S) handles numeric numbers and text numbers differently than SUMPRODUCT.

See if this does what you want...

=SUMPRODUCT(--(TEXT('\\serverpath\[reference.xlsx]2011'!$E$1:$E$100,"000000")=TEXT(A2,"000000")),--(LEFT('\\serverpath\[reference.xlsx]2011'!$G$1:$G$100,2)="I-"))

 

[TMcG]

Negative on both formulas. Am I out of reasonable options? I can reformat these columns to be whatever is easiest.

 

[T. Valko]

Negative on both formulas. Am I out of reasonable options? I can reformat these columns to be whatever is easiest.

Can you post a small sample file that shows the formula returning 0?

Don't need to see 1000's of rows of data just a few dozen should do.

You can use a free file hosting site if need be.