Counting cells with equal number except decimal number

alexius

New Member
Joined
Apr 18, 2018
Messages
4
Hi,

I have a column of family members:

151.1
151.2
151.3
151.4

I like to see the total in the family in the next column as follows:


151.1 4
151.2
151.3
151.4

Which formulas to use ?
Thanks for your support !
Alexius
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi Alexius,

Welcome to MrExcel!!

Here's one way (just change the range from A2:A10 to suit your needs):

=COUNTIFS($A$2:$A$10,">=151",$A$2:$A$10,"<152")

Regards,

Robert
 
Upvote 0
Additional option:

Book1
AB
1151.14
2151.20
3151.30
4151.40
5200.12
6200.20
Sheet1
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(A1,1)="1",LEFT(A1,SEARCH(".",A1,1)-1),0),$A$1:$A$6,1)))
 
Upvote 0
Additional option:
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]151.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]151.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]151.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]151.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]200.1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]200.2[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(A1,1)="1",LEFT(A1,SEARCH(".",A1,1)-1),0),$A$1:$A$6,1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, the second option is what I need !
 
Upvote 0
Hi,

I applied the formula. It works well for number 10 - 20. If I extend the range, cells with 0 (point nr .2, .3, etc) get a number. And the family size does not show correctly for cells with .1.

=SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(B6,1)="1",LEFT(B6,SEARCH(".",B6,1)-1),0),$B$6:$B$50,1)))

[TABLE="width: 339"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Data base formula family size[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/04/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Registration
date[/TD]
[TD]ID NEW VERSION[/TD]
[TD]Family Size
(calculated)[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]13.6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]14.7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]15.1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]15.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/12/2017[/TD]
[TD]16.6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]17.1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]17.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]17.3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25/12/2017[/TD]
[TD]17.4[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 339"]
<colgroup><col width="113" span="2" style="width:85pt"> <col width="113" style="width:85pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 113"]25/12/2017[/TD]
[TD="class: xl66, width: 113"]17.5[/TD]
[TD="class: xl70, width: 113"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.1[/TD]
[TD="class: xl70"]8[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.2[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.3[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.4[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.5[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.6[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.7[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]23/12/2017[/TD]
[TD="class: xl66"]18.8[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]19.1[/TD]
[TD="class: xl70"]5[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]19.2[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]19.3[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]19.4[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]19.5[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.1[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.2[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.3[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.4[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.5[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/08/2017[/TD]
[TD="class: xl66"]20.6[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]21.1[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]21.2[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]21.3[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]21.4[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]22.1[/TD]
[TD="class: xl70"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]22.2[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]22.3[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]12/06/2017[/TD]
[TD="class: xl66"]22.4[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
</tbody>[/TABLE]

Little down:
extending range to 1500
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(B348,1)="1",LEFT(B348,SEARCH(".",B348,1)-1),0),$B$346:$B$1500,1)))


[TABLE="width: 339"]
<colgroup><col width="113" span="2" style="width:85pt"> <col width="113" style="width:85pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 113"]11/02/2016[/TD]
[TD="class: xl68, width: 113"]102.1[/TD]
[TD="class: xl70, width: 113"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]102.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]102.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]102.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]103.1[/TD]
[TD="class: xl70"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]103.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.1[/TD]
[TD="class: xl70"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.5[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]104.6[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]105.1[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]105.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]105.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]105.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]106.1[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]106.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]106.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]106.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]107.1[/TD]
[TD="class: xl70"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]107.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]107.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]107.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]108.1[/TD]
[TD="class: xl70"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]108.2[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]108.3[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]108.4[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
[TR]
[TD="class: xl66"]11/02/2016[/TD]
[TD="class: xl68"]108.5[/TD]
[TD="class: xl70"]165[/TD]
[/TR]
</tbody>[/TABLE]


Cannot solve it :-(. Appreciate help.
 
Upvote 0
check if this helps:

Book1
ABC
1RegistrationID NEW VERSION
2date
326-11-20170.17
427-11-20170.20
528-11-20170.30
629-11-20170.40
730-11-20170.50
81-12-20170.60
92-12-20170.70
103-12-20171.14
114-12-20171.20
125-12-20171.30
136-12-20171.40
147-12-20172.18
158-12-20172.20
169-12-20172.30
1710-12-20172.40
1811-12-20172.50
8212-6-201722.14
8312-6-201722.20
8412-6-201722.30
8512-6-201722.40
8611-2-2016102.14
8711-2-2016102.20
8811-2-2016102.30
8911-2-2016102.40
9011-2-2016103.12
9111-2-2016103.20
9211-2-2016104.16
9311-2-2016104.20
9411-2-2016104.30
9511-2-2016104.40
9611-2-2016104.50
9711-2-2016104.60
9811-2-2016105.14
9911-2-2016105.20
10011-2-2016105.30
10111-2-2016105.40
10211-2-2016106.14
10311-2-2016106.20
10411-2-2016106.30
10511-2-2016106.40
10611-2-2016107.14
10711-2-2016107.20
10811-2-2016107.30
10911-2-2016107.40
Sheet1
Cell Formulas
RangeFormula
C3=SUMPRODUCT(--(RIGHT(B3,1)="1")*(INT(VALUE(SUBSTITUTE($B$3:$B$114,".",",")))=INT(VALUE(SUBSTITUTE(B3,".",",")))))
 
Last edited:
Upvote 0
Here is another formula for you to consider... with it, you do not have to specify the row number for the last cell containing data as it will figure that out on its own. It also only displays the total next to the first ID and displays the empty text string ("") for all the other cells (that sure make it easier to spot the totals without all those distracting intervening zeros. Put the following formula in cell C3 and copy it down to or beyond the last data cell...

=IF(RIGHT(B3)="1",SUMPRODUCT(--(INT(INDIRECT("B3:B"&COUNT(B:B)+2))=INT(B3))),"")

Note: If you copy the formula down well past the end of your current data, you can come along later and add more data below your existing data and the formula will automatically show the totals for each new ID in Column B.
 
Last edited:
Upvote 0
Hi,

thanks your effort !!! Quite a complex formula.

However didn't get it work :-(
Copied the table below and inserted the formula.
It gives a #value error, also after formatting cells as number.

If it is not too much, your advice please. Else I will insert manually.

Thanks,

Alexius


check if this helps:
ABC
RegistrationID NEW VERSION
date

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]26-11-2017[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]27-11-2017[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]28-11-2017[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]29-11-2017[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]30-11-2017[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1-12-2017[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2-12-2017[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3-12-2017[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4-12-2017[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]5-12-2017[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6-12-2017[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]7-12-2017[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]8-12-2017[/TD]
[TD="align: right"]2.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]9-12-2017[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]10-12-2017[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]11-12-2017[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"]12-6-2017[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"]12-6-2017[/TD]
[TD="align: right"]22.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]84[/TD]
[TD="align: right"]12-6-2017[/TD]
[TD="align: right"]22.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]85[/TD]
[TD="align: right"]12-6-2017[/TD]
[TD="align: right"]22.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]86[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]102.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]87[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]102.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]88[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]102.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]89[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]102.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]90[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]103.1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]91[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]103.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]92[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.1[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]95[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]96[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]97[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]104.6[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]98[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]105.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]99[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]105.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]100[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]105.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]101[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]105.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]102[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]106.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]103[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]106.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]104[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]106.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]105[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]106.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]106[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]107.1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]107[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]107.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]108[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]107.3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]109[/TD]
[TD="align: right"]11-2-2016[/TD]
[TD="align: right"]107.4[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=SUMPRODUCT(--(RIGHT(B3,1)="1")*(INT(VALUE(SUBSTITUTE($B$3:$B$114,".",",")))=INT(VALUE(SUBSTITUTE(B3,".",",")))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
if you want to display the number only next to the first ID then use this

=IF(RIGHT(B1)="1",SUMPRODUCT(--(INT($B$1:$B$100)=INT(B1))),"")
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top