Countif formula isn't adding up right!

[Stacie]

So I have two columns of data, Department and College.

I am using the countif formula to count how many times a department appears
in the department column, but then when I sum the counts, it doesn't add up
correctly. I am almost positive that everything is spelt right, since I just
copied and pasted. So I am wondering if the formula isn't counting them
right and advice for that or should I be using a different formula. Any help
would be appreciated! Thank you!

 

[bpeltzer]

It would really help to see the formula you used. But one fairly common
issue that could cause the counts to come up short would be using relative
references in your range and then autofilling the formula. That is:
=countif(A1:A10,"Department 1"). Instead, you should use
=countif($A$1:$A$10,"Department 1"). Those dollar signs make the references
absolute, so they won't change as you copy the formula down or across.
Without them, the references change as you autofill so that you're not
pointing to the entire source table.

 

[Sandy Mann]

It may be that the Department names have spaces or non-breaking spaces at
either end which is making the COUNTIF() fail to match some of the data.
Try:

=COUNTIF(A1:A100,"*Department Name*")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Stacie,

Posting you formula would be a good start but on the assumption the formula
is correct the usual suspects are rogue spaces and numbers that look like
numbers but are in fact text and I'm always particularly suspicious when the
data are pasted.

So check you department column for rougue spaces and verify you numbers
coulumn with

=isnumber(a1)

which will return TRUE for a number or FALSE for text.

Mike

 

[Stacie]

One more part is that I am counting the departments from one worksheet and
putting the results in another worksheet.

The formula I use is: =COUNTIF(March!C3:C3227,B14)

C3:C3227 is my list of departments, in the March worksheet, and then B14 is
the cell that I am trying to count. So instead of typing in the text in the
Criteria e.g: "Accounting" I am simply selecting the cell e.g: B14.

After giving it more thought I am positive that it isn't spelling or spacing
because I used a VLookup formula and that would have caught any spelling or
spacing issues.

Could entering the formula in a different worksheet then the cells I am
trying to reference be causing the problem? It seems to be where the problem
comes in, I'm not sure.

Thank you!

 

[Gord Dibben]

COUNTIF will work across sheets so that is not your problem.


Gord Dibben MS Excel MVP