COUNTIF Formula for Non-Numeric Data Not Working

[Mezzi]

Hello everyone I previously used the formula =COUNTIF(C2:C2030,"KSA") to count how many fields were KSA related for example.

However, I am trying to use this formula now and every time I try it the formula just returns 0.

Can anyone assist?

Thanks in advance for your help.

[Jonmo1]

That formula would return 0 if thare are no cells in C2:C2030 that = "KSA"

[Fotis1991]

..However, I am trying to use this formula now and every time I try it the formula just returns 0.

What do you mean? How do you use the formula?

[Mezzi]

That formula would return 0 if thare are no cells in C2:C2030 that = "KSA"

The particular sheet I am working on has 5060 rows of this number 2309 are KSA. The formula does not return a value of 2309 it returns a value of 0.

What do you mean? How do you use the formula?

The very same formula I typed I have tried using to no avail.

Also I am trying to return the smallest value in other related spreadsheets but it seems the min function will not work in a sheet with many fields. I tried using the formula to find the smallest value in a column with over 2000 fields and it keeps returning -1 and this is not correct.

I also tried using gnumeric and libre office and keep getting the same incorrect results.

Ahhhhhhgggghhh

[Jonmo1]

I think you should post a sample book that is giving the incorrect results.

[Fotis1991]

If you try to increase the range?

Let's say....

=COUNTIF(C2:C10000,"KSA")

[Mezzi]

Hey everyone I have uploaded the spreadsheet showing the issue.

If you look in cell G5062 and see the COUNTIF formula which returns 0 although the correct result should be a figure over 115.

The Countif formula of =COUNTIF(G2:G50,"KSA") just isn't working and I am totally baffled.

Thanks in advance for your help in figuring out why it isnt working.

EDIT: I have figured it out, put an asterick before the last quotation mark.

Thanks again for all your replies, it was good to know people were trying to help.

[Jonmo1]

You have trailing spaces in all the cells in column A
So A1901 DOES NOT = "PORTLAND"
It actually = "PORTLAND____"

the _ represent spaces, forum software removes extra spaces..

[Fotis1991]

You have so many spaces in column A.

One way could be this one.

=COUNTIF(A1901:A2016,"*Portland*")

[Mezzi]

I did not realize that there were replies before I did the edit.

I also did not know there were spaces after the names. This spreadsheet was created from specialized software and I did not do the abstraction. Thanks for teaching me something new since I had no idea spacing could affect the formula.

I had found out about the asterick by picking up the book Microsoft Office Excel 2007 Formulas and Functions for Dummies. I use the asterick like this =COUNTIF(G2:G50, "KSA*") Fotis I will use your way just in case there happens to be spaces at the front as well of any words as well.

Thanks again to you all.

[Fotis1991]

..................

[Mezzi]

Hey guys me again using another COUNTIF to determine the number of new applications made daily the formula is
=COUNTIF(D2:D8614,"2-Jan-12") my only problem is that when I drag this formula down trying to get the daily results for the entire year the dates are changing to "2-Jan-12" "3-Jan-12" and so on. Unfortunately, what is changing is the cell range which needs to remain constant.

How can I have the dates change by day without having to manually change the date in 365 rows. Also how can I have the cell range remain constant.

Thanks in advance and sorry for the bother.

[daddylonglegs]

List your dates in an adjacent column, e.g. in G2 put the first date and copy it down the column, it will increase by 1 day each time - then use this formula in H2 also copied down

=COUNTIF(D:D,G2)

[popipipo]

=COUNTIF($B$2:$B$8614;40908+ROW())

40909 stands for 1-1-2012

[Mezzi]

List your dates in an adjacent column, e.g. in G2 put the first date and copy it down the column, it will increase by 1 day each time - then use this formula in H2 also copied down

=COUNTIF(D:D,G2)

Daddylonglegs you are the best, thanks so much

=COUNTIF($B$2:$B$8614;40908+ROW())

40909 stands for 1-1-2012

Popipipo thanks for your reply, unfortunately this didn't work for me but I thank you nonetheless

You guys are the best.

[popipipo]

unfortunately this didn't work for me

Why not
See this example

Or did I misunderstood you