Excel External Reference Formula

[Eric Marqeuz]

I am trying to count the number of times a name appears on a calendar, however; the areas where the names are on the calendar are populated via a reference from an external excel workbook. 

I am trying to use the COUNTIF function however, it does not work on external references that are larger than one cell. For example, the external reference is " {='[Copy of August Monthly_PEX(974).xlsx]Friday, August 04, 2017 (216)'!$C$15:$C$20} ". This reference is typed, but the actual name of the employee is in the cell. However, the COUNTIF function does not seem to realize the actual name is there and instead of counting up, the counter remains at zero.

The puzzling part of this whole thing is that in other parts of the calendar where only a single cell is referenced even if the referenced cell is a Merged Cell, ie $A$1:$A$2, the proper counters count up one using the COUNTIF function.

I have attatched the calendar in question for reference but not the referenced workbook.

[#2]

Convert COUNTIF to SUM(IF()).

for example: Change COUNTIF(A6:BD15,BE13) to SUM(IF(A6:BD15=BE13, 1, 0)).

[#3]

Hi Eric,

I guess the issue is not in formula but in your data where you have some extra invisible symbol. For example, in your sample file copy cell S13 (with EarlyS) and paste as value in any empty cell. Tale LEN() of it. It gives 7, not 6.

[#4]

I agree with Sergei Baklan . The list of names in some of the sheets of your source workbook seem to have an additional space suffixed to some of the names, example: "EarlyS" is named as "EarlyS ". So the correction should be in the source workbook of the external links.

[#5]

That's not the space, another ascii symbol

[#6 · Accepted solution]

Okay,  now i'm not on mobile and checked the file. There is Line Feed at the end of each string, CHAR(10) other words.

Better in the source replace CHAR(10) to nothing, alternatively add CHAR(10) in calculations like

=COUNTIF(A6:BD15,BE13&CHAR(10))+COUNTIF(A24:BD32,BE13&CHAR(10))+COUNTIF(A41:BD49,BE13&CHAR(10))+COUNTIF(A58:BD66,BE13&CHAR(10))+COUNTIF(A75:BD83,BE13&CHAR(10))

As for alternativies which functions to use that doesn't matter - use any one with which you are more comfortable.

[#7]

Thank you so much for your assistance! I was aware of that extra character and deleted it on a temporary copy on a desktop, but my surface doesn't have a num pad so I couldn't delete it on my tablet. Turns out, using the Find and Replace function works as well. Alt+010 on a num pad is the code for a line break...full stop... what ever you want to call it! But had it not been for your keen eyes I would have spent many extra hours drying my eyes by staring at a computer screen! Thank you again!