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.



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:



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

comments powered by Disqus