1

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.

Improve this question
5
  • can you explain with some example with input and expected output?
    – Siva Charan
    Apr 19, 2012 at 17:43
  • edited with an example.
    – dierre
    Apr 19, 2012 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, 2012 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, 2012 at 18:07
  • Refer to my answer.
    – Siva Charan
    Apr 19, 2012 at 18:32

2 Answers 2

Reset to default
3

Your expected formula:

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

OR

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

OUTPUT

3
Improve this answer
4
  • 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, 2012 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, 2012 at 18:55
  • ok but that will check also "Universityprofessor" right?
    – dierre
    Apr 19, 2012 at 19:07
  • @dierre: yes, it will count this one too.
    – Siva Charan
    Apr 19, 2012 at 19:09
1

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.

Improve this answer

You must log in to answer this question.

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