Countifs returning 0 for items being searched in where they exist

[sevas1994]

Hi there,

Instead of removing duplicates, I tried to count number of duplicated items using countifs. In column A, I have IDs of transactions (they are not unique), in B date, C description, D amount. In Column E, I wrote the following formula: =countifs(A:A, A1, B:B, B1, C:C, C1, D:D, D1) and I have zeros. What do zeros mean in this case? I would expect all the unique ones to return one, but somehow I have zeros. Why do I get them?


Many thanks in advance.

 

[Scott T]

Countifs uses and logic so every condition has to be true. Your formula is counting how many duplicates of row 1 there are. If you only want the duplicate IDs then this should work

=COUNTIFS(A:A,A1)

 

[Jonmo1]

The logic is sound, you should get at least 1 for each row.

I can think of only a couple causes for 0's.
A circular reference, look in Error Checking on the Formulas Tab

Calculation set to manual on the Formulas Tab

Also blanks in the ranges, if A1 is blank, it will cause a 0 for the countifs in E1

 

[sevas1994]

Thanks for the reply . I checked the circular reference and calculation method, made sure that there are no blanks, but none of it seems to be the case unfortunately. I'm getting the same problem with sumifs by the way.

The logic is sound, you should get at least 1 for each row.

I can think of only a couple causes for 0's.
A circular reference, look in Error Checking on the Formulas Tab

Calculation set to manual on the Formulas Tab

Also blanks in the ranges, if A1 is blank, it will cause a 0 for the countifs in E1

 

[sevas1994]

It seems like the only choice left is to combine the contents in columns A to D using & and then use only countif

The logic is sound, you should get at least 1 for each row.

I can think of only a couple causes for 0's.
A circular reference, look in Error Checking on the Formulas Tab

Calculation set to manual on the Formulas Tab

Also blanks in the ranges, if A1 is blank, it will cause a 0 for the countifs in E1

 

[Jonmo1]

If none of those ideas turned out to be the issue, then I have no idea what's wrong.
It will be next to impossible to tell without seeing the actual document.

Can you post an example file at google docs or something ?