Forum Discussion
Solved
Countif function do not count correctly
I figure out that countif counts 1.20 as 1.1 It is possibleto find correct solution with "exact if and sum" formulas. you can find the excel file in the attachment.
your number is not format as number but as text i have sent you a solution you can check it if there is any problem so please clear your criteria for better understanding alparslan
Hi, Thanks for your reply. However my data is not actually an excel number. It is an identity. This list is a production BOM. Some identities have two or more points like 1.2.1 or 1.4.3.1
- Thank you. It solved the issue. I understand that CountIf is a little bit dangerous. 1.1 and 1.20 is same value on date. 1.1.20. I will prefer to use exact function to count values. I learned =SUMPRODUCT(EXACT(A2;A2:A21)*1) formula is ok also.
Nope, the same here are 1.1 and 1.10 since COUNTIF converts texts which present numbers to numbers, if don't use CHAR(173).
With SUMPRODUCT() you don't need EXACT, it compares texts as they are. Just
=SUMPRODUCT(--($A$2:$A$21=A2))is enough.
