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!

 

[Ruddles]

Sorry, I was going to suggest using SQL but i'm not sure that would be any better.

 

[Ruddles]

My suggestion was going to be to use SQL against the reference file to do all the counting and then dump the resulting (much smaller) table into a spare worksheet. Then you could use that worksheet to look stuff up in.

The downside is you have to run the SQL query as often as you think the reference file might be changing (although this shouldn't take too long), and rather than your worksheet being 'live' to the reference file, it would be a snapshot of what the reference file was like when the SQL was run.

Is that acceptable?

Have you ever written SQL or VBA before? Are you even allowed to use VBA in your establishment?

At some point you may have to provide some sample data for people to work with, even if it's sanitised to remove classified details.

 

[TMcG]

Given the computer system that it is running on, I am not permitted to use Basic or SQL. Part of the issue with providing a sample, even sanitized, is that the computer system the files exist on is an entirely separate system that is not connected to unsecured internet.

 

[TMcG]

I appreciate you trying to help. Its a tricky operation. If I could use basic, my life would be a lot easier. Any suggestions you have would be great, even if I have to create a new reference file or do something in a roundabout fashion.

 

[T. Valko]

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!

Try this...

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

I would avoid using the entire columns as range references. Use smaller specific ranges.

 

[barry houdini]

Try using SUMPRODUCT which can work with a closed file

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

 

[TMcG]

Thanks to both of you for your input.

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

Barry- Your formula will not execute unless I replace the column designators with a smaller range designator like Biff suggested. When I replace the ranges, it also returns zero.

The file that I am referencing currently has 51619 entries. This is why I am trying hard to get this equation running. Do you guys have any other suggestions or am I SOL? Again, I really appreciate your assistance. If I'm out of options, then I'm out of options.

 

[barry houdini]

Presumably the COUNTIFS formula is giving you a non-zero result when you have the file open?

COUNTIFS and SUMPRODUCT can work slightly differently with text v numeric data - what sort of entries do you have in A2 and/or 2011'!$E$:$E$?

 

[TMcG]

Barry-

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.