I want to be able to determine how many times a person's particular ID (e.g. #123
) has appeared in a separate column (e.g. column C
) given a name (e.g. Bob
).
I have:
- Let column A consist of several names. (
Bob
)- Let column B consist of IDs relating to the person's name. (
#123
)- Let column C consist of those same IDs, however it may contain duplicates of the IDs (might have three
#123
s)
The IDs are side-by-side with each person's respective name.
How can I use the result of a VLOOKUP
as the criteria (second argument) of the COUNTIF
function?
If I use VLOOKUP
separately it returns the correct value that I'm trying to count. =VLOOKUP(Bob, column A:column B, 2, FALSE)
will return #123
If I use COUNTIF
separately it returns the correct count value as well. =COUNTIF(column C, #123
) will return 3
I've tried =COUNTIF(column C, VLOOKUP(Bob, column A:column B, 2, FALSE))
but it returns 0
.
I've tried referring the criteria for COUNTIF
to a separate cell with the VLOOKUP
formula but it still returns 0
.
So it seems like it stops working when I combine the two together. What might the problem be, and what might be an alternative?
@Steve Martin has a valid point, but assuming Bob
is text, your formula is correct though the syntax should be:
=COUNTIF(C:C,VLOOKUP("Bob",A:B,2,0))
If this is what you are using and it is returning 0
rather than the correct result the most likely reason is that the #123
in ColumnB is not the same as any #123
in ColumnC. (In turn, the most likely reason for that is probably one or more trailing spaces - that do not show on inspection of cells in ColumnsB:C).
To check, just copy one of the #123
s from ColumnC over the top of the first #123
in ColumnB that is immediately to the right of Bob
. Should you then see a result greater than 0
you are "on the right track" and may want to apply TRIM to remove surplus spaces.
In short, VLOOKUP attempts to match actual cell content rather than visible cell content and it recognises a difference between #123
and #123
even though it does not display the two differently.