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.
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.
Thank you. It is less completed.
