# How to return a count using COUNTIF with VLOOKUP

Fbooom Source

I want to return a count of how many times the word FREE appears in a column (column B) which is tied to a style code (column E)

1. Column B contains the "FREE" values
2. Column C represents the style code for the free item (there will be duplicate style codes in this column)
3. Column E contains the unique style codes (no duplicates)
4. Column F is my count column

I've attempted different iterations of each formula below to no avail:

1. `=countif(\$B\$2:\$B\$1000,"*"&"FREE"&"*")` - wildcards used to account for other characters in the string.
• This formula does a full count of the value "FREE" in column B, however it does not count for the specific style code alone (e.g. - style "SARM" was FREE 10 times)
2. `=countif(\$B\$2:\$B\$1000,VLOOKUP("FREE",\$B\$2:\$C\$1000,2,FALSE))`
• Returns "0"

The problem I am having is that I am unsure of how to link column E (unique style code) to column C, then return how many times FREE appears (column B) per style in C.

Any help is greatly appreciated.

excelexcel-formulavlookupcountif

answered 2 years ago Sanjoy #1

Can you upload the excel or provide screenshot ? It appears that there are two solutions to this. Either you can use VBA with InSTR command. With formula you can use =FIND(cell). if it returns a value, it means it exisits and if it throws an error it means it does not exisit. Then you can sum it up.

answered 2 years ago Harlekuin #2

I recommend adding a new column that represents whether "FREE" is present in the B column:

``````=NOT(ISERROR(FIND("free",B8)))
``````

Etc.

Then you can `COUNTIFS` on the new column equaling TRUE, and the style codes