countifs value to be in decimal

[ExcelNewbie2020]

I have this table below, i want every recurrence of acct. and no. will count and accumulate by decimal..
In my table below, comlumn B (no. column) can be change 2 to 5 while the recurence for both acct and no. can accumulate to 5 or more. I already have this formula in column C (expected result) =IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=2,B2&".2",IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=3,B2&".3",IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=4,B2&".4",IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=5,B2&".5",B2)))). Its working up to 5 occurence and i need to add more.. Is there any other way to simplify the formula using Excel version 2019?.. many thanks

acct	no.	Expected result
102​	1​	1​
103​	1​	1​
102​	1​	1.2​
104​	1​	1​
102​	1​	1.3​
103​	1​	1.2​
103​	1​	1.3​
103​	1​	1.4​
103​	1​	1.5​
104​	1​	1.2​
104​	1​	1.3​
104​	1​	1.4​
105​	1​	1​
102​	1​	1.4​
102​	1​	1.5​
105​	1​	1.2​
105​	1​	1.3​

 

[zero269]

count and accumulate by decimal

Out of curiosity, is your no. column formatted to display decimals?
I know it's basic, but I just needed to ask since that's the first thing I would look at...

Another thing to consider is to use the TEXT Function:
=TEXT(G3,"0.0")
This will display a 1 in a cell as 1.0 for example.
Although I've never used it with COUNTIFS.

 

[ExcelNewbie2020]

Out of curiosity, is your no. column formatted to display decimals?
I know it's basic, but I just needed to ask since that's the first thing I would look at...

View attachment 84214

Another thing to consider is to use the TEXT Function:
=TEXT(G3,"0.0")
This will display a 1 in a cell as 1.0 for example.
Although I've never used it with COUNTIFS.

thank you.. the idea of my formula is whenever the count become 2 or more the result based on if function would be 2=1.2, 3=1.3, and so on...nothing to do with the format cells

 

[FormR]

Is there any other way to simplify the formula using Excel version 2019?

Hi, if I've understood correctly, you could try:

Book3
	A	B	C
1	acct	no.	Expected result
2	102	1	1.1
3	103	1	1.1
4	102	1	1.2
5	104	1	1.1
6	102	1	1.3
7	103	1	1.2
8	103	1	1.3
9	103	1	1.4
10	103	1	1.5
11	104	1	1.2
12	104	1	1.3
13	104	1	1.4
14	105	1	1.1
15	102	1	1.4
16	102	1	1.5
17	105	1	1.2
18	105	1	1.3
Sheet1

Cell Formulas
Range	Formula
C2:C18	C2	=B2&"."&COUNTIFS(A$2:A2,A2)

 

[ExcelNewbie2020]

Hi, if I've understood correctly, you could try:

Book3
	A	B	C
1	acct	no.	Expected result
2	102	1	1.1
3	103	1	1.1
4	102	1	1.2
5	104	1	1.1
6	102	1	1.3
7	103	1	1.2
8	103	1	1.3
9	103	1	1.4
10	103	1	1.5
11	104	1	1.2
12	104	1	1.3
13	104	1	1.4
14	105	1	1.1
15	102	1	1.4
16	102	1	1.5
17	105	1	1.2
18	105	1	1.3
Sheet1

Cell Formulas
Range	Formula
C2:C18	C2	=B2&"."&COUNTIFS(A$2:A2,A2)

why i didn't think of that.. a simple as that... thanks man..