A simple COUNTIF formula that is returning zero

[Louise Grills]

Hi guys

Hope you have had a good holiday/New Year break.

Ive been browsing forums for the last couple of hours about this, and I am stumped. Most of the posts relating to this actually refer to formulas which are far more complicated than the one I am having issues with, so I am starting a fresh. Sorry if there is a related query somewhere, but this is for a work project which is due tomorrow so I am running out of time to sit and browse!

My problem seems stupidly simple.

I have 1 spreadsheet with 4 tabs: 2014 Summary, 2014 Dashboard, 2013 Summary, 2013 Dashboard

The dashboards contain lots of information about requests for resource I have received over the year, and will receive over this year, and there is 1 colume which tells me the status of the project, which is what I need to count.

The summary tabs break this down to show how many of these resource requests have been completed, how many are ongoing, and how many are not started.

On each of the summary tabs I have 3 COUNTIF formulas which count the 'status' column on the dahsboard tab. The column will only contain 'complete' 'ongoing' and 'not started'

Here is an example of the formula I am using
=COUNTIF('2013 Dashboard'!I2:I166,"Ongoing")

The "ongoing" bit is then replaced with the other options.

My issues is this: 5 out of the 6 formulas are working. However, the above formula, trying to count the ongoing projects for 2013, is returning zero, when I know from manually counting them there are 7.

Ive re-entered the formula. Ive checked that there are no leading or following spaces. Its all formatted to text. Its all spelt the same as what I am looking for. The range is fine (the entries only go up to 140)

I am out of ideas! I use COUNTIF all the time and its working fine for everything else so I am completely stumped.

Hopefully one of you guys can see something I am missing, I probably cant see the wood for the trees as I have been staring at it so long.

Thanks in advance

 

[Somendra Misra]

Hi Louise Grills,

Welcome to the Forum.
As a new user you must go through below link.
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Now regarding your problem, the formula seems to be ok. But if it is not delivering you the proper result, I would advise you to upload a portion of your file with the data range and range where you are applying this formula. That will help readers to go through the error in a better way.

Regards!

 

[Faseeh]

Hi Louise,

Please check out text "Ongoing" in cell for extra spaces, i guess something is making the text in the cells different from "Ongoing" that is causing this problem, any extra space or line break character can be the cause.

 

[SirJB7]

Hi, Louise Grills!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

 

[Hui]

What Faseeh said is that "Ongoing" is not equal to "Ongoing " or " Ongoing"
Check for extra spaces

 

[KenU]

Louise,

I notice that your formula is using relative references. Depending on how and where it is used and copied, this could also be a factor in the behavior you have described. You might confirm that each formula is referring to the proper range.

Regards,
Ken

 

[NARAYANK991]

Hi Louise ,

You might have already found out what the problem was ; in case you haven't , you can use this to troubleshoot.

When ever a COUNTIF formula does not give the result you want , put in the following formula , entered as an array formula , and see what you get.

=SUM(IF({"Ongoing"," Ongoing","Ongoing ","On Going"}=A1:A6,1,0))

In the above formula , within the brackets {...} put in all the possible variations , excluding variations in case i.e. upper case and lower case don't matter ; you can include variations which have leading , trailing and in between space characters. A1:A6 is the range of interest.

In case you get a number which is more than what you get using COUNTIF , then it is clear that some of your data needs to be corrected.

Narayan