Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"
"  doctoR"
"fireman"

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.

share|improve this question
    
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
up vote 3 down vote accepted

Your expected formula:

=COUNTIF(A:A,"*doctor*")

OR

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

OUTPUT

3
share|improve this answer
    
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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .