Countif similar text values such as 3.1, 3.10, 4.1, 4.10

[shyy]

Hey guys, I'm having trouble with counting numbers that are similar, example 3.1 and 3.10. We have violation codes that I am trying to count, problem is that both 3.1 and 3.10 come up with the same exact value although both are different digits. codes can go from 3.1 up to 3.15. Is there away to differentiate these numbers?

With the table below I should have (4) 3.1 codes & (3) 3.10 codes. What I get for both codes is 4 (treating both as 3.1)

[TABLE="width: 100"]
<tbody>[TR]
[TD]3.1[/TD]
[TD]4.10[/TD]
[TD]3.10[/TD]
[/TR]
[TR]
[TD]3.2[/TD]
[TD]4.1[/TD]
[TD]3.1[/TD]
[/TR]
[TR]
[TD]3.10[/TD]
[TD]3.15[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]4.5[/TD]
[TD]3.1[/TD]
[TD]3.1[/TD]
[/TR]
[TR]
[TD]4.1[/TD]
[TD]4.1[/TD]
[TD]3.10[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your time

 

[AhoyNC]

If the codes are text try using SUMPRODUCT:

Excel Workbook
	A	B	C	D	E	F
1	3.1	4.10	3.10		3.1	4
2	3.2	4.1	3.1		3.10	3
3	3.10	3.15	5.5		4.1	3
4	4.5	3.1	3.1		4.10	1
5	4.1	4.1	3.10		4.5	1
6					5.5	1
Sheet

 

[shyy]

Thanks that did the trick, can't wait to try it at work. Curious why doesn't the countif work?

If the codes are text try using SUMPRODUCT:

	A	B	C	D	E	F

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:38px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]3.10[/TD]

[TD="align: right"]3.1[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]3.1[/TD]

[TD="align: right"]3.10[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]3.10[/TD]
[TD="align: right"]3.15[/TD]
[TD="align: right"]5.5[/TD]

[TD="align: right"]4.1[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]3.1[/TD]

[TD="align: right"]4.10[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]3.10[/TD]

[TD="align: right"]4.5[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]5.5[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =SUMPRODUCT(--($A$1:$C$5=E1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

This way seems to work using COUNTIF

Excel Workbook
	A	B	C	D	E	F
1	3.1	4.10	3.10		3.1	4
2	3.2	4.1	3.1		3.10	3
3	3.10	3.15	5.5		4.1	3
4	4.5	3.1	3.1		4.10	1
5	4.1	4.1	3.10		4.5	1
6					5.5	1
Sheet

 

[63falcondude]

COUNTIF sees 3.1 and 3.10 as the same.

Edit: Unless you do what AhoyNC showed in post 4. I've never seen that before.