The problem is this: I have a column full of nouns. These nouns could be something like: "Doctor", "doctor" or "DOCTOR " (with a space at the end). Clearly I need to normalize those records before using COUNTIF. Rigth now I'm just using a new column "in the middle" in which I normalize the data and then I use COUNTIF on those normalized data.

I'm doing this because I can't understand how I can normalize the range taken for COUNTIF as first argumenti. Is it possibile to apply functions to the first argument? Or is there a more suitable function I can use in this case?

I have at home the 2007 and at the office 2010 so you can aim for both version.

Requested example:

Column A has:

"Doctor "
"  doctoR"

I need to do something like COUNTIF( NORMALIZE(A1:A4) ; "doctor" ) because " Doctor" will not match "doctor" because of the space. Right now I'm doing NORMALIZE(A1:A4) in B1:B4 and then COUNTIF( B1:B4; "doctor")

NORMALIZE is something I just inventend, not a real function of course.

  • can you explain with some example with input and expected output? – Siva Charan Apr 19 '12 at 17:43
  • edited with an example. – dierre Apr 19 '12 at 17:51
  • So you mean if you do countif(A1:A4, "doctor"), your expected output should be 3 or 1? – Siva Charan Apr 19 '12 at 17:56
  • I don't think you understood my problem. My expected output is 3, and 3 I have. I'm just asking if there's a way to normalize the range inside the COUNTIF and not using two steps in the process. – dierre Apr 19 '12 at 18:07
  • Refer to my answer. – Siva Charan Apr 19 '12 at 18:32

Your expected formula:





  • If I have something like "University professor" should I use the wildcard in the middle? Does it accept + like for regexp,like "University +professor"? (1 or more). I do not have backtick on my kb. It should be 4 spaces between University and professor. – dierre Apr 19 '12 at 18:39
  • @dierre: + won't do. If you set University*professor, you get total count. And if you want specifically to check 4 spaces between University and professor then your formula can be University professor – Siva Charan Apr 19 '12 at 18:55
  • ok but that will check also "Universityprofessor" right? – dierre Apr 19 '12 at 19:07
  • @dierre: yes, it will count this one too. – Siva Charan Apr 19 '12 at 19:09

If you have a limited number of nouns and variations, than you can use some addition in your formula and capture all the possible variations (capitalization shouldn't matter, but spaces does):

=COUNTIF(A2:A4,"doctor")+COUNTIF(A2:A4,"doctor ")+COUNTIF(A2:A4," doctor")

Using a helper column or column "in the middle" is also a good way (and sometimes a necessary way) to manage shaping the data for analysis. Often they can help keep your formulas from getting too complex.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.