COUNTIF from another file

[Schturman]

Hi to all
I need to count one name from another file. The name can appear in 3 diff variations, for example:
Nati
Nati*
Nati**

The name for search is always clean (without * or **), for example in cell CP27 you can see name Nati

I used command:

=COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27&"*")

and it working perfectly and count for me all 3 variation, but when I close the source file, the result shown as #VALUE (yes already know that excel can't show result when source file is closed )

I started to use this format for count (from this post):

=COUNT(IF('[filename.xlsm]sheet_name'!$D$7:$I$55=CP27,1)

It working, but problem is - it count only clean name, that mean only Nati, and I need use it like this to count all 3 variations:

=COUNT(IF('[filename.xlsm]sheet_name'!$D$7:$I$55=CP27,1),IF('[filename.xlsm]sheet_name'!$D$7:$I$55=CP27&"*",1),IF('[filename.xlsm]sheet_name'!$D$7:$I$55=CP27&"**",1))

Same with SUMPRODUCT formula...
Can I shorten this formula somehow and it will count all 3 variations ?
Thanks

 

[Rows N Columns]

Can you try if this formula works?
=COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27)+COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27&"~*")+COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27&"~*~*")

 

[jdellasala]

One thing I found when referencing another file - when you create the formula and both files are open (of course!), the reference will appear as noted by @Rows N Columns . However, when you close the file referenced, the entire path will be added to the formula. At that point the referenced file does not (shouldn't!) have to be opened for the formula to work.
So COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27) would become COUNTIF('C:\Users\USERID\Desktop\[filename.xlsm]sheet_name'!$D$7:$I$55,CP27).

 

[Fluff]

Countifs does not work with closed files, so you need to use sumproduct

=SUMPRODUCT(--(LEFT([filename.xlsm]sheet_name!$D$7:$I$55,LEN(CP27))=CP27))

 

[jdellasala]

Countifs does not work with closed files, so you need to use sumproduct

=SUMPRODUCT(--(LEFT([filename.xlsm]sheet_name!$D$7:$I$55,LEN(CP27))=CP27))

I stand corrected! I just tried it with SUMPRODUCT and it didn't work there either . At the time (long before I even knew what Power Query was!) I was using VLOOKUP (no XLOOKUP back then), and both files were in the same local folder. I tried it before posting and it SEEMED to work, but now it doesn't appear to work at all. Well, there's always Power Query!

 

[Fluff]

Sumproduct does work with closed workbooks.

 

[RoryA]

I just tried it with SUMPRODUCT and it didn't work there either

SUMPRODUCT does work with closed files, so whatever you tried, the issue wasn't with SUMPRODUCT itself.

 

[Schturman]

Can you try if this formula works?
=COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27)+COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27&"~*")+COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27&"~*~*")

COUNTIF not work if source file is closed.

One thing I found when referencing another file - when you create the formula and both files are open (of course!), the reference will appear as noted by @Rows N Columns . However, when you close the file referenced, the entire path will be added to the formula. At that point the referenced file does not (shouldn't!) have to be opened for the formula to work.
So COUNTIF('[filename.xlsm]sheet_name'!$D$7:$I$55,CP27) would become COUNTIF('C:\Users\USERID\Desktop\[filename.xlsm]sheet_name'!$D$7:$I$55,CP27).

COUNTIF not work if source file is closed, I wrote it in my post.

Countifs does not work with closed files, so you need to use sumproduct

=SUMPRODUCT(--(LEFT([filename.xlsm]sheet_name!$D$7:$I$55,LEN(CP27))=CP27))

This formula working perfectly !!! Thank you very much !
I tried it without LEFT and LEN, this is a reason it not worked for me.
Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Schturman]

By the way, I checked the same way with COUNT formula with LEFT and LEN:

=COUNT(IF(LEFT('[filename.xlsm]Sheet_name'!$D$7:$I$55,LEN(A4))=A4,1))

And it also working perfectly Count and show result also if source file is closed
@Fluff , thanks again for idea !