• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS returning wrong result

tarynmahon

tarynmahon

Member
I can't seem to work out whats going wrong with a simple COUNTIFS, I've even just broken it down to a COUNTIF and thats not even working, its returning a figure over 1000000 different to what it should be, the only thing that's a little bit different about it is that all critera are looking up does not equal.

Its almost as if its counting all the unused lines at the bottom of the data, but I've tried deleting these rows just in case there's some formatting that Ive missed but still not joy, does anyone have any ideas?
 
Tarynmahon


Can you post a sample file with your data as it will be difficult to analyse without that

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
No I cannot, which is why I didnt, its just a normal COUNTIF I have other countifs working on the same worksheet, I am very capable of doing countifs I was just looking for possible reasons why it might return a wrong figure
 
Tarynmahon


I'd try the following:


+ Check the Range sizes are all the same

+ Check that the data is all Numbers and doesn't contain special or leading/trailing spaces

+ Check that any criteria columns don't have Leading/Trailing spaces

+ Check that any criteria are correctly formed

+ Double check that you haven't got the Ranges and Criteria mixed up


After all this try the formula on a small set of data where you can manually check it


If you can't post the data can you at least post the formula ?
 
I was going to make a suggestion but after reading tarynmahons second post again I thought whoa...theirs some one with a bit of attitude, still some one who is so capable does not need my two penny worth.
 
Bobhc


You have to be a bit careful of not reading too much between the lines on posts.

There are many reasons for ambiguity between what the poster meant and what the reader interpreted the post as meaning including language of the poster and reader and stress (poster and reader)

I have learnt just to let small things slide and give people the benefit of the doubt

There have been times here at the Forums where it has been pushed too far and then I will respond appropriately
 
Change the formulas from looking at whole Columns to just the data

eg:

Wrong: =IFERROR(COUNTIFS($J:$J,"<>FG",$G:$G,""),0) = 1022179

Right: =IFERROR(COUNTIFS($J8:$J27903,"<>FG",$G8:$G27903,""),0) = 1501
 
Good day Hui

You are quite correct, I read the post as if it was written with a lot of arrogance and extreme bad manners, a lot of people try to help and when someone comes over like a bull in a china shop then I just see it as wrong. I offer my apologise to tarynmahon
 
Bobhc


In the 3 years I've been helping out here there has only been 2 or 3 blatantly rude posters. I think it is better to not start public fights as readers don't deserve to and shouldn't be exposed to that here and it just demeans the value of the site for all concerned. In the end we can either not help them or ultimately just remove them from the forum.
 
You must log in or register to reply here.
Back
Top