Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
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. "Stacie" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
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 Replace @mailinator.com with @tiscali.co.uk "Stacie" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
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" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif formula isn't adding up right!
COUNTIF will work across sheets so that is not your problem.
Gord Dibben MS Excel MVP On Tue, 30 Sep 2008 13:18:01 -0700, Stacie wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif formula | Excel Discussion (Misc queries) | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
CountIf formula help | Excel Discussion (Misc queries) | |||
countif formula | Excel Discussion (Misc queries) | |||
Adding an AND Statement to COUNTIF | Excel Worksheet Functions |