0

I have the following data on sheet 2

Name        
Mark Jacob
James Smith

On sheet 1 I have a name column in column a and i want to count the total number of times the name in column a appears in the name column on sheet 2. Like so:

Name Total Mark Jacob 1 Harry Potter 0

I am using the following countif function:

=COUNTIF('Sheet2'!K:K,"*"&"A2"&"*")

For some reason however my value always returns 0

However if I replace my Cell reference A2 from my function with 'Mark' this gives me a result of 1. Why is this?

Please can someone show me where I am going wrong? Thanks

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
Mark harris
  • 525
  • 15
  • 39

2 Answers2

1

Try this:

=COUNTIF(Sheet2!K:K,"*"&A2&"*")
Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Thanks for the suggestion however this still isn't working – Mark harris Oct 29 '15 at 19:17
  • For some reason if I delete the value from my cell on sheet 2 and retype it in exactly as was, it works but not sure what could be causing this, theres definitely no spaces or anything – Mark harris Oct 29 '15 at 19:20
  • There must be something funky with the data. I've tested this here several times and it never fails to work. – Excel Hero Oct 29 '15 at 19:27
1

You are treating A2 as a literal, so you need to remove the quotes from it. An added benefit, would be if you are using the variable, then you can also copy and paste it to a range. Also, double check the formulas for each cell to ensure it is mapping to the correct column. Eg.:

=COUNTIF(Sheet1!$D:$D,"*"&F5&"*")
ergonaut
  • 6,929
  • 1
  • 17
  • 47