1

I have a whole bunch of ID's in columns like this:

  A         B        C
id234     id002
id294     id103      2
id700     id023
id201     id294
id892     id500
id932     id294
id600     id100
id402     id031
id725     id802
id702     id031
id435     id799
id031     id311     0
id007     id846
id111     
id027

My idea is to use Excel function COUNTIFS to count how many times an ID from column "A" appears in column "B" and display the output in column "C". So, in the first cell of column "C" =COUNTIFS($B1:$B13,A1). It works just as expected for, say, id294. (It finds two matches of it in column "B" and prints out 2). The same should happen for id031. There are a couple of matches in column "B" so it should print 2 as well. Instead, it prints out 0.

What I suspect that is happening is that as the function COUNTIFS finds id294 first in column "A" and them its two matches in column B" it does this job, but as it finds id031 in column "A" after they appear in column"B" it doesn't do the count properly and prints out 0 as if there was no match found. Does order matter here? How can I fix this?

Also, when I use function =IF(ISNA(VLOOKUP(A1,$B1:$B13,1,FALSE)),"No","Yes") to print out "Yes" or "No" depending whether or not there is a match, it prints Yes if the value is in column "A" first but prints No if the value is in column "B" first.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Diego Menezes
  • 85
  • 2
  • 10

1 Answers1

0

Please anchor the start of the B range (ie $B$1 rather than $B1). At present when you copy down, $B1 goes to $B2 etc and the search range progressively decreases in size (effectively - it stays the same number of cells but row by row a cell at the top with a value is replaced by a blank cell from below). The whole range 'slides' down. Try: 

=COUNTIFS($B$1:$B13,A1)

Or, since in your example "not found" is showing as blank: 

=IF(COUNTIFS($B$1:$B13,A1)>0,COUNTIFS($B$1:$B13,A1),"")
pnuts
  • 58,317
  • 11
  • 87
  • 139