Countif function goes crazy

Can you post the actual formula?
Or even better, a sample workbook that is displaying this behavior?


FYI, a normal countif does not require CTRL + SHIFT + ENTER

Hi all,
thanks for the quick replies.

* I was certain that with Countif(s) and Sumif(s) you MUST use ctrl+shift+enter. I'm surprised that's not the case. So, when do I and when don't I ?

* Anyway, I got the exact same results with just Enter.

* The data I'm working on is list of company names, so it's almost exclusively textual.
It's also the reason why I can post my data, unfortunately


Generally speaking, here is the formula:

=COUNTIF('sheet with original data'!$B$2:$B$28124,'sheet after removing duplicates'!B2)

the company names appear in the 2nd column on both.

Thanks again all!

Up until 10 mins ago I was certain countif requires it and will produce wrong numbers else.

Strange, I was certain I sent a reply.
It seems to give the right numbers with a PT (Thanks for suggesting!), but I'm interested to understand what went wrong with the Countif I performed... for my general knowledge and for some more data manipulation I'm going to do on a subset of the data set at the next stage.

Originally Posted by Norie

What happens if you create a pivot table from the original table with the company name as a row field and count of company name in the data section?

Can you post a sample book that displays this behavior?

Unfortunately, I can't. I know it's hard to answer questions without actually looking at the data...... and I appreciate you guys trying to help

Originally Posted by Jonmo1

Can you post a sample book that displays this behavior?

The SUMPRODUCT provides very similar results (I compared to the countif). The major difference is that the value countif 'counted' 3170 times is only counted once with the sumproduct.
However, the numbers still don't match what the pivot table provides, for example. I still get a higher number than the total # of rows in the original data set.

Originally Posted by daddylonglegs

Try using SUMPRODUCT like this

=SUMPRODUCT(('sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2)+0)

SUMPRODUCT is less efficient than COUNTIF but it has fewer inconsistencies - do you get the same results?

Is there a way to obfuscate the original data without changing its properties ?

Originally Posted by Jonmo1

Can you post a sample book that displays this behavior?

Originally Posted by ANS

The SUMPRODUCT provides very similar results (I compared to the countif). The major difference is that the value countif 'counted' 3170 times is only counted once with the sumproduct.

And what value was that?
Does it contain any wildcards like * or ?

Countif uses wildcards, sumproduct does not

I'm just concerned it will "work" just fine with any dummy data I generate specifically for this purpose.
I'll try

Originally Posted by Norie

ANS

Can you not mock up a sample workbook?

Ha!
It includes "<" !!
Why is it counting it 3170 times??

What are other wildcards and how will COUNTIF treat them?

Countif will always give wrong results when there are characters such as * or ? in the cell value?

Originally Posted by Jonmo1

And what value was that?
Does it contain any wildcards like * or ?

Countif uses wildcards, sumproduct does not

countif is treating the < literally as the Less than symbol

so if the string in the cell is "<Hello there"
Countif is reading it as "Less than Hello there"

Any text string beginning with a letter lower than H, like Frank, Bill, Alice, will be counted.

The wild card would come into play if you had say

John Doe
John Smith
John*

Then the countif on John* would be counted 3 times.

Holy Mozes!

Is there a way to "fix" it so I could still use the countif function?

What other wildcards should I watch out for?

Thank you!

Originally Posted by Jonmo1

countif is treating the < literally as the Less than symbol

so if the string in the cell is "<Hello there"
Countif is reading it as "Less than Hello there"

Any text string beginning with a letter lower than H, like Frank, Bill, Alice, will be counted.

I think it's been clearly established that a pivot table is the best solution..


I would recommend doing a find/replace on both the source data and the DeDuped data to remove < > * ? characters
When doing the * and ? characters, use ~* and ~?, this makes it treat them as literal characters instead of wildcards.

Pivot table would work great if you need no further analysis.
If you do, it becomes cumbersome.

Thanks so much for your help!

Originally Posted by ANS

Thanks so much for your help!

You're welcome.

You know how cumbersome pivot tables can be... so for instance if I wanted to have more columns with new data, calculations and manipulations, I would indeed need to copy paste all values. This may be an acceptable solution if you only perform the analysis once, but it's quite possible I'll add more records to the original list, etc. So I'll have to re-do the pivot table, copy & paste again, and so on.

Thanks for the help again. I'm sorry I don't have enough reputation to go around!

Originally Posted by Norie

ANS

How would the pivot prevent/hamper further analysis?

If you really don't want the pivot then just copy the results from it, paste them somewhere and delete the pivot table.

Can you please explain this formula to me? I thought sumproduct takes two arrays (?) of numbers and sums up the product of each two corresponding values.
The way this formula is laid out, there are no 2 arrays, there's a "=" sign, and top top it all off, there's +0

Would really appreciate some explanation.

Originally Posted by daddylonglegs

Try using SUMPRODUCT like this

=SUMPRODUCT(('sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2)+0)

SUMPRODUCT is less efficient than COUNTIF but it has fewer inconsistencies - do you get the same results?

I think I kinddof understand Thanks!

Originally Posted by daddylonglegs

Yes, SUMPRODUCT normally multiplies 2 or more arrays/ranges and then sums the results but I used it here because it can normally be used without "array entry", so in fact you can use SUM if you want

This part

='sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2

returns an array of TRUE/FALSE values TRUE when the entry matches B2, FALSE when it doesn't

If you then use some mathematical operation on that array which doesn't alter the value, like "double negation" --, or *1 or +0 as I used then that converts the TRUE/FALSE value to 1/0 values and if you sum those you effectively get a count of matches, so you could use

=SUM(('sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2)+0)

or with an IF function like

=SUM(IF('sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2,1,0))

....but both of those (unlike the SUMPRODUCT version) require you to "array enter" the formula, i.e. confirm with CTRL+SHIFT+ENTER