Counting cells with equal number except decimal number

[alexius]

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

 

[Trebor76]

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

 

[jorismoerings]

Additional option:

Book1
	A	B
1	151.1	4
2	151.2	0
3	151.3	0
4	151.4	0
5	200.1	2
6	200.2	0
Sheet1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(A1,1)="1",LEFT(A1,SEARCH(".",A1,1)-1),0),$A$1:$A$6,1)))

 

[alexius]

Additional option:

	A	B
1	151.1	4
2	151.2	0
3	151.3	0
4	151.4	0
5	200.1	2
6	200.2	0

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =SUMPRODUCT(--ISNUMBER(SEARCH(IF(RIGHT(A1,1)="1",LEFT(A1,SEARCH(".",A1,1)-1),0),$A$1:$A$6,1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks, the second option is what I need !

 

[alexius]

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)))

Data base formula family size
20/04/2018
Registration date	ID NEW VERSION	Family Size (calculated)
26/12/2017	13.1	6
26/12/2017	13.2	0
26/12/2017	13.3	0
26/12/2017	13.4	0
26/12/2017	13.5	0
26/12/2017	13.6	0
26/12/2017	14.1	7
26/12/2017	14.2	0
26/12/2017	14.3	0
26/12/2017	14.4	0
26/12/2017	14.5	0
26/12/2017	14.6	0
26/12/2017	14.7	0
26/12/2017	15.1	2
26/12/2017	15.2	0
26/12/2017	16.1	6
26/12/2017	16.2	0
26/12/2017	16.3	0
26/12/2017	16.4	0
26/12/2017	16.5	0
26/12/2017	16.6	0
25/12/2017	17.1	4
25/12/2017	17.2	0
25/12/2017	17.3	0
25/12/2017	17.4	0

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

25/12/2017	17.5	6
23/12/2017	18.1	8
23/12/2017	18.2	6
23/12/2017	18.3	6
23/12/2017	18.4	6
23/12/2017	18.5	6
23/12/2017	18.6	6
23/12/2017	18.7	6
23/12/2017	18.8	6
12/08/2017	19.1	5
12/08/2017	19.2	6
12/08/2017	19.3	6
12/08/2017	19.4	6
12/08/2017	19.5	6
12/08/2017	20.1	6
12/08/2017	20.2	6
12/08/2017	20.3	6
12/08/2017	20.4	6
12/08/2017	20.5	6
12/08/2017	20.6	6
12/06/2017	21.1	0
12/06/2017	21.2	6
12/06/2017	21.3	6
12/06/2017	21.4	6
12/06/2017	22.1	0
12/06/2017	22.2	6
12/06/2017	22.3	6
12/06/2017	22.4	6

<colgroup><col width="113" span="2" style="width:85pt"> <col width="113" style="width:85pt"> </colgroup><tbody>
</tbody>

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)))

11/02/2016	102.1	4
11/02/2016	102.2	165
11/02/2016	102.3	165
11/02/2016	102.4	165
11/02/2016	103.1	2
11/02/2016	103.2	165
11/02/2016	104.1	6
11/02/2016	104.2	165
11/02/2016	104.3	165
11/02/2016	104.4	165
11/02/2016	104.5	165
11/02/2016	104.6	165
11/02/2016	105.1	4
11/02/2016	105.2	165
11/02/2016	105.3	165
11/02/2016	105.4	165
11/02/2016	106.1	4
11/02/2016	106.2	165
11/02/2016	106.3	165
11/02/2016	106.4	165
11/02/2016	107.1	4
11/02/2016	107.2	165
11/02/2016	107.3	165
11/02/2016	107.4	165
11/02/2016	108.1	5
11/02/2016	108.2	165
11/02/2016	108.3	165
11/02/2016	108.4	165
11/02/2016	108.5	165

<colgroup><col width="113" span="2" style="width:85pt"> <col width="113" style="width:85pt"> </colgroup><tbody>
</tbody>

Cannot solve it . Appreciate help.

 

[jorismoerings]

check if this helps:

Book1
	A	B	C
1	Registration	ID NEW VERSION
2	date
3	26-11-2017	0.1	7
4	27-11-2017	0.2	0
5	28-11-2017	0.3	0
6	29-11-2017	0.4	0
7	30-11-2017	0.5	0
8	1-12-2017	0.6	0
9	2-12-2017	0.7	0
10	3-12-2017	1.1	4
11	4-12-2017	1.2	0
12	5-12-2017	1.3	0
13	6-12-2017	1.4	0
14	7-12-2017	2.1	8
15	8-12-2017	2.2	0
16	9-12-2017	2.3	0
17	10-12-2017	2.4	0
18	11-12-2017	2.5	0
82	12-6-2017	22.1	4
83	12-6-2017	22.2	0
84	12-6-2017	22.3	0
85	12-6-2017	22.4	0
86	11-2-2016	102.1	4
87	11-2-2016	102.2	0
88	11-2-2016	102.3	0
89	11-2-2016	102.4	0
90	11-2-2016	103.1	2
91	11-2-2016	103.2	0
92	11-2-2016	104.1	6
93	11-2-2016	104.2	0
94	11-2-2016	104.3	0
95	11-2-2016	104.4	0
96	11-2-2016	104.5	0
97	11-2-2016	104.6	0
98	11-2-2016	105.1	4
99	11-2-2016	105.2	0
100	11-2-2016	105.3	0
101	11-2-2016	105.4	0
102	11-2-2016	106.1	4
103	11-2-2016	106.2	0
104	11-2-2016	106.3	0
105	11-2-2016	106.4	0
106	11-2-2016	107.1	4
107	11-2-2016	107.2	0
108	11-2-2016	107.3	0
109	11-2-2016	107.4	0
Sheet1

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(--(RIGHT(B3,1)="1")*(INT(VALUE(SUBSTITUTE($B$3:$B$114,".",",")))=INT(VALUE(SUBSTITUTE(B3,".",",")))))

 

[snjpverma]

Try this
=SUMPRODUCT(--(INT($B$1:$B$100)=INT(B1)))

 

[Rick Rothstein]

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.

 

[alexius]

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:

	A	B	C
1	Registration	ID NEW VERSION
2	date
3	26-11-2017	0.1	7
4	27-11-2017	0.2	0
5	28-11-2017	0.3	0
6	29-11-2017	0.4	0
7	30-11-2017	0.5	0
8	1-12-2017	0.6	0
9	2-12-2017	0.7	0
10	3-12-2017	1.1	4
11	4-12-2017	1.2	0
12	5-12-2017	1.3	0
13	6-12-2017	1.4	0
14	7-12-2017	2.1	8
15	8-12-2017	2.2	0
16	9-12-2017	2.3	0
17	10-12-2017	2.4	0
18	11-12-2017	2.5	0
82	12-6-2017	22.1	4
83	12-6-2017	22.2	0
84	12-6-2017	22.3	0
85	12-6-2017	22.4	0
86	11-2-2016	102.1	4
87	11-2-2016	102.2	0
88	11-2-2016	102.3	0
89	11-2-2016	102.4	0
90	11-2-2016	103.1	2
91	11-2-2016	103.2	0
92	11-2-2016	104.1	6
93	11-2-2016	104.2	0
94	11-2-2016	104.3	0
95	11-2-2016	104.4	0
96	11-2-2016	104.5	0
97	11-2-2016	104.6	0
98	11-2-2016	105.1	4
99	11-2-2016	105.2	0
100	11-2-2016	105.3	0
101	11-2-2016	105.4	0
102	11-2-2016	106.1	4
103	11-2-2016	106.2	0
104	11-2-2016	106.3	0
105	11-2-2016	106.4	0
106	11-2-2016	107.1	4
107	11-2-2016	107.2	0
108	11-2-2016	107.3	0
109	11-2-2016	107.4	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C3 =SUMPRODUCT(--(RIGHT(B3,1)="1")*(INT(VALUE(SUBSTITUTE($B$3:$B$114,".",",")))=INT(VALUE(SUBSTITUTE(B3,".",","))))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[snjpverma]

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))),"")