Forum

Notifications
Clear all

How to get dynamic sums for different number

5 Posts
2 Users
0 Reactions
62 Views
(@tlyqtyl)
Posts: 21
Eminent Member
Topic starter
 

Suppose I have a COUNT function to return the number in Column B based on my input. The result will be 1, 2, 3...dynamically.

Suppose I have a list data in Column D

If the count result is 1, I want to get a sum of D1:D5 (Always 5) in E1

If the count result is 2, I want to get a sum of D1:D5 & D2:D6 in E2

If the count result is 3, I want to get a sum of D1:D5 with D2:D6 with D3:D7 in E3

etc.,

How to get the formula to achieve this? (Please no vba)

 
Posted : 20/05/2021 8:13 pm
(@mynda)
Posts: 4761
Member Admin
 

Please upload a sample Excel file. It's very difficult to imagine the scenario you describe in my head.

Thanks,

Mynda

 
Posted : 21/05/2021 2:35 am
(@tlyqtyl)
Posts: 21
Eminent Member
Topic starter
 

Thanks. Mynda. The original file is a little bit complicated. Please see attached File.

Had Original data from E5:E9.

1) Input B10, get E10 after calculation. Then need normalized E6:E10 to F6:F10 by FormularnF6=E6*5/(Sum($E$6:$E$10)   accordingly.

2) Input B11, Get E11 after calculation. Then need normalized F7:F10 & E11 to G7:G11 by similar formula.rn rnI just want to consolidated formula for column F & column G. So that I can get use one formula to get value like column L.

 
Posted : 21/05/2021 10:17 am
(@mynda)
Posts: 4761
Member Admin
 

If you have Microsoft 365 then I'd say you could write a recursive lambda to combine those formulas into one. Not my area of expertise, sorry. Maybe someone else can help if you get stuck.

 
Posted : 21/05/2021 9:11 pm
(@tlyqtyl)
Posts: 21
Eminent Member
Topic starter
 

Thanks anyway.

 
Posted : 21/05/2021 11:04 pm
Share: