ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 293
- Office Version
- 365
- Platform
- Windows
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
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 |