0

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.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Fbooom
  • 1
  • 1
  • 1
  • 1
    can you add a screenshot of your data. looks like you can use `COUNTIFS` – nightcrawler23 Feb 22 '17 at 03:06
  • 1
    as nightcrawler23 said, using `countifs` you could use something like `=countifs($B$1:$B$1000,"*FREE*",$C$1:$C$1000,"SARM")` – ballsy26 Feb 22 '17 at 03:09
  • @Fbooom Problem solved or need help. – Harun24hr Feb 22 '17 at 03:50
  • @ballsy26 Thanks for the direction. This is one of the iterations I attempted, without limiting the second criteria to a specific style code - this also did not yield the results I was looking for. – Fbooom Feb 22 '17 at 18:29
  • after having a look at your screenshot you could use something like `=COUNTIFS($C$1:$C$1000,"*FREE*",$B$1:$B$1000,B1)` http://imgur.com/a/8AbOG – ballsy26 Feb 22 '17 at 21:39
  • @ballsy26 Thanks, this seems closer to what I am looking to accomplish. However, the count is off. Here is a screen shot with the formula you provided: http://imgur.com/ELiiAYy - As you can see the "AME2S" does not have an entry in column B, but the count from the formula in column G still returns 1. – Fbooom Feb 23 '17 at 05:15
  • i think i get you now, how about this `=COUNTIFS($C$1:$C$6,"*FREE*",$B$1:$B$6,E5)` – ballsy26 Feb 23 '17 at 05:42

2 Answers2

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.

Sanjoy
  • 336
  • 6
  • 17
  • @nightcrawler23 Apologies, missed the window to properly edit the comment above: https://www.dropbox.com/s/aapu104aibnn08m/sheet2.png?dl=0 As you can see, Styles "AMES" & "AMEM" (in column B) contain "FREE" in column C, however, column G returns 0 results. I am essentially attempting to count "FREE" by style. Thanks again for the help! – Fbooom Feb 22 '17 at 17:12
  • I think if you are looking for this. You have to create a helper column which would first find free for qualification. Then you can use the same with countif. http://prntscr.com/ed3dmh – Sanjoy Feb 25 '17 at 04:30
0

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

Tom Malkin
  • 2,134
  • 2
  • 19
  • 35