1

I am hoping someone can help with this Excel 2016 issue. I have done extensive searching on the net and believe I have found the formula to address my requirements. Before sharing the formula, let me explain my need.

I am creating a budget spreadsheet. There is currently two sheets in the spreadsheet. sheet1 contains data for categories and keywords. Column A contains different categories like Car Fule, Salary, Bank Fee, etc. Column B contains keywords to be search for within the transaction description. An example could be:

car fule | Mobile

Sheet2 contains all the transaction data. All the columns other then column c isn't relevant for this issue. An example of a transaction description could be: "EXP mobile Hobbitville 2221 MiddleEarth".

I am trying to create a formula that does a search in the defined cell for column c, against the keywords found in sheet` column b. Then once a match is found, perform a indirect function call to give the category of the transaction. I cannot get the initial search to work.

Formula that should work is:

=countif(c2,"*"&sheet1!$b$1:$b$85&"*")

This always gives a value of 0 saying it hasn't been found. I am using ctrl shift enter and ctrl enter to make the an array formula. If I press f9, I get all the values defined from sheet 1 as {0, 0, ...} (or something like that) which is what I expect. In that array, I should get at least one element as a '1' (true value). If I can fix this part of the formula or find a better method, then I can build the rest of the formula. Any one with ideas?

Aussie Smurf
  • 11
  • 2
  • Is [this](http://stackoverflow.com/questions/32860792/count-and-highlight-keywords-within-phrases/32878493#32878493) what you are trying to achieve? –  Apr 22 '17 at 03:41

2 Answers2

0

This is the COUNTIF syntax,

COUNTIF(range, criteria)

It should be the other way around,

=COUNTIF(C:C,"*"&Sheet1!$B1&"*")

Let me know if its not working

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
  • based upon this blog, the formula I gave earlier should work. http://blog.contextures.com/archives/2012/10/09/find-text-with-index-and-match/ I have downloaded their sample and it works fine for the sample. But when I place it in my spreadsheet, it doesn't work. I am even using ctrl shift enter or ctrl enter to tell Excel the formula is an array. – Aussie Smurf Apr 24 '17 at 04:57
0

Realize this is pretty old but in 2023 I had the same annoying issue where COUNTIF() was not working when I was pointing to a range in a different worksheet. Everything I tried ended up as 0 but if I copied the data into the current worksheet, the COUNTIF() worked fine.

I finally/accidentally figured out that if I renamed my worksheet name from the default "Sheet1", "Sheet2", "Sheet3" it would work.

So here's how I started and it would always come back as 0:

=COUNTIF(Sheet1!B:B,"*PhraseIWasLookingFor*")

And here's what I finally got working:

=COUNTIF(MyRenamedWorksheet!B:B,"*PhraseIWasLookingFor*")
Christopher
  • 1,639
  • 19
  • 22