Dynamic range of column in sum range with criteria

[jeetusaini85]

Hi Guys,

I hope all is well at your end.

I need your help to create a formula that I am trying to create for 2 days and now I give up.

Below is the table in which column A has multiple same values and in B column the value will always be 0 in the first cell corresponding to column A and in 2nd cell of the B column will be a random value.

So, in column B3 I want to add 10 including B2 value and so on till the column A value change. Like, B3 would be 5+10=15, B4=5+15+10=30....

Hope, It makes sense, Please assist.

A	B
1	0
1	5
1	(B1+B2)+10=15
1	(B1+B2+B3)+10=30
1	(B1+B2+B3+B4)+10=50
2	0
2	10
2	(B6+B7)+10=20
2	(B6+B7+B8)+10=40
2	(B6+B7+B8+B9)+10=80
3	0
3	6
3	so on
3
3

 

[Eric W]

Maybe:

Book1
	A	B
1	A	B
2	1	0
3	1	6
4	1	16
5	1	32
6	1	64
7	2	0
8	2	8
9	2	18
10	2	36
11	2	72
12	3	0
13	3	19
14	3	29
15	3	58
16	3	116
Sheet4

Cell Formulas
Range	Formula
B2:B16	B2	=IF(A2<>A1,0,IF(COUNTIF(A$1:A1,A2)=1,RANDBETWEEN(1,20),SUMIF(A$1:A1,A2,B$1:B1)+10))

The random number is an integer between 1 and 20, you can switch this if needed easy enough.

 

[jeetusaini85]

Hi Eric,

I appreciate your help.

Working like a charm but what if column A has alphanumeric value like below, will RANDBETWEEN work on this as well.

LNETA03619-200014378
LNETA03619-200014378
LNETA03619-200014378
LNETA03619-200014379
LNETA03619-200014379
LNETA03619-200014379

 

[jeetusaini85]

For more clarification, below is the actual table. You can see that i am calculating in AB4 column in which AA is also included, now I want to add AB3 to get the value in AB4 and so on till the column B value changes.

 

[Eric W]

I'm confused as to what you want. There is no issue if the data in column A is text or not, the formula handles it fine. Also, when you said "random number", is this a number you wanted randomly generated, or is it a number you want to enter and have the formulas handle? You now seem to be adding numbers horizontally and vertically. Can you explain how you want the sums to be created?

If possible, can you try to use the XL2BB tool? See the button in the response box. This will allow you to post a sample of your sheet that I can copy, so I don't have to manually enter all that data.

 

[jeetusaini85]

Hi Eric,

Please find below the data and snapshot of the formula I am using.

You can see that I am calculating in U4 column in which column T is also included in the calculation sum, now I want to add U3 to get the value in U4 and so on till the column A value changes.

Please let me know how can I attach a sample XL file for your reference.

loan_no

INSTL_DATE

INSTL_AMOUNT

PRIN_COMP

INT_COMP

RUNNING_NORMAL_INT

INTEREST_RATE

Nature of Installment

New Due Date

Part Payment Date

Part Payment Amount

Part Payment Days

Mar-20​

Apr-20​

May-20​

Jun-20​

Jul-20​

Aug-20​

Total Moratorium Tenure

Mar-20​

Apr-20​

May-20​

Jun-20​

Jul-20​

Aug-20​

LNETA03619-200014376

20-03-2020​

11731500​

11427400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

31-03-2020

0

0

10​

0​

0​

0​

0​

0​

10

43178

0

0

0

0

0

LNETA03619-200014376

20-03-2020​

11631500​

11327400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

05-04-2020

100000

5

0​

5​

0​

0​

0​

0​

5

0

21485

0

0

0

0

LNETA03619-200014376

20-03-2020​

11531500​

11227400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

15-04-2020

100000

10

0​

10​

0​

0​

0​

0​

10

0

42601

0

0

0

0

LNETA03619-200014376

20-03-2020​

11431500​

11127400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

25-04-2020

100000

10

0​

10​

0​

0​

0​

0​

10

0

42233

0

0

0

0

LNETA03619-200014376

20-03-2020​

11331500​

11027400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

15-05-2020

100000

20

0​

0​

20​

0​

0​

0​

20

0

0

84513

0

0

0

LNETA03619-200014376

20-03-2020​

11231500​

10927400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

10-06-2020

100000

25

0​

0​

0​

25​

0​

0​

25

0

0

0

105499

0

0

LNETA03619-200014376

20-03-2020​

11231500​

10927400​

304100​

304100​

13.25%​

BULLET REPAYMENT

20-06-2020​

20-06-2020

0

10

0​

0​

0​

10​

0​

0​

10

0

0

0

42199

0

0

LNETA03619-200014377

07-04-2020​

10300000​

10000000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

31-03-2020

0

0

0​

0​

0​

0​

0​

0​

0

0

0

0

0

0

0

LNETA03619-200014377

07-04-2020​

10200000​

9900000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

04-04-2020

100000

4

0​

0​

0​

0​

0​

0​

0

0

0

0

0

0

0

LNETA03619-200014377

07-04-2020​

10000000​

9700000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

15-04-2020

200000

11

0​

8​

0​

0​

0​

0​

8

0

29444

0

0

0

0

LNETA03619-200014377

07-04-2020​

9900000​

9600000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

25-04-2020

100000

10

0​

10​

0​

0​

0​

0​

10

0

36438

0

0

0

0

LNETA03619-200014377

07-04-2020​

9700000​

9400000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

15-05-2020

200000

20

0​

0​

20​

0​

0​

0​

20

0

0

71888

0

0

0

LNETA03619-200014377

07-04-2020​

9600000​

9300000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

10-06-2020

100000

25

0​

0​

0​

25​

0​

0​

25

0

0

0

89601

0

0

LNETA03619-200014377

07-04-2020​

9600000​

9300000​

300000​

300000​

13.25%​

BULLET REPAYMENT

07-07-2020​

07-07-2020

0

27

0​

0​

0​

0​

27​

0​

27

0

0

0

0

97659

0

LNETA03619-200014378

10-05-2020​

20000000​

20000000​

400000​

0​

13.25%​

BULLET REPAYMENT

10-08-2020​

31-03-2020

0

0

0​

0​

0​

0​

0​

0​

0

0

0

0

0

0

0

LNETA03619-200014378

10-05-2020​

19936622​

19900000​

400000​

36622​

13.25%​

BULLET REPAYMENT

10-08-2020​

05-04-2020

100000

5

0​

0​

0​

0​

0​

0​

0

0

0

0

0

0

0

LNETA03619-200014378

10-05-2020​

19881267​

19700000​

400000​

181267​

13.25%​

BULLET REPAYMENT

10-08-2020​

30-04-2020

200000

25

0​

0​

0​

0​

0​

0​

0

0

0

0

0

0

0

LNETA03619-200014378

10-05-2020​

19672139​

19600000​

400000​

72139​

13.25%​

BULLET REPAYMENT

10-08-2020​

10-05-2020

100000

10

0​

0​

1​

0​

0​

0​

1

0

0

7240

0

0

0

LNETA03619-200014378

10-05-2020​

19471403​

19400000​

400000​

71403​

13.25%​

BULLET REPAYMENT

10-08-2020​

20-05-2020

200000

10

0​

0​

10​

0​

0​

0​

10

0

0

71666

0

0

0

LNETA03619-200014378

10-05-2020​

19378138​

19300000​

400000​

78138​

13.25%​

BULLET REPAYMENT

10-08-2020​

01-06-2020

100000

11

0​

0​

0​

11​

0​

0​

11

0

0

0

78774

0

0

LNETA03619-200014378

10-05-2020​

19374165​

19100000​

400000​

274165​

13.25%​

BULLET REPAYMENT

10-08-2020​

10-07-2020

200000

39

0​

0​

0​

0​

39​

0​

39

0

0

0

0

280363

0

LNETA03619-200014378

10-05-2020​

19310896​

19100000​

400000​

210896​

13.25%​

BULLET REPAYMENT

10-08-2020​

10-08-2020

0

30

0​

0​

0​

0​

0​

30​

30

0

0

0

0

0

218061

 

[Eric W]

Thank you for the copyable data. It seems to match up with your picture. There is no way to directly attach a spreadsheet on this forum. Your options are to use the XL2BB tool (best, but some places do not allow installing add-ons), or paste a range like you did here (which should be adequate), or to upload it to a file sharing service like DropBox and post a link here.

Given that I now have a copy of your data, I'm still confused as to what you want. I see that for every value in L2:R23, there is a matching value in T2:Y23, and I assume you want to sum them up in some manner. Could you manually calculate the desired values for T2:Y8 and explain in words how you arrived at those values?

 

[jeetusaini85]

Hi Eric,

You can download the sample file from the below link, the actual output which i want is mentioned in the column AA to AF.

I hope this clarifies my requirements.

Thanks for your support in advance.

sample.xlsx

drive.google.com

 

[jeetusaini85]

Hi Eric,

I have integrated your formula with mine and got the desired results but need to check with you whether I have done right or wrong, please have a look:

in U3

=ROUND(($C3+SUM(T3:INDEX(T:T,MATCH(A:A,A:A,0)))+IF(A3<>A2,0,IF(COUNTIF(A$1:A2,A3)<>1,SUMIF(A$1:A2,A3,AU$1:AU2))))*$G3/360*N3,0)

 

[jeetusaini85]

Please ignore previous formula:

=ROUND(($C3+SUM(T3:INDEX(T:T,MATCH(A:A,A:A,0)))+IF(A3<>A2,0,IF(COUNTIF(A$1:A2,A3)<>1,SUMIF(A$1:A2,A3,U$1:U2))))*$G3/360*N3,0)