Sum of cell in different rows (dynamic)

[Exequiel3k]

Hey y'all!

Essentially, is there a way to automatically sum the values from different rows/columns dynamically:

Example: G = F - (lookup value) - B
G25 = F23 - B25

I.e. It will look at F25/ if nothing --> F24/if nothing --> F23/ Has a value

(Please see excel file)

I've tried several different ways of stating it in IF statement without success

Any help would be much appreciated

[JohnTopley]

Used helper column in H

h17 and copy down

=IF(F17=0,H16,ROWS($1:1))

in G17

=IF(SUM($B17:$E17)>0,INDEX($F$17:$F$27,$H17)-SUM($B17:$E17),"")

Copy down

[José Augusto]

Hi Exequiel

try this formula in G17 and copy down.

Please Login or Register  to view this content.

See the file data.xlsx for clarification

[Exequiel3k]

Thanks for taking the time to reply back

The thing is new data will be fed to it consistently. In the data excel file (i provided) it worked flawlessly but the main excel file it doesn't (#value! error).. maybe because there are over 3k rows?
Would something have to change around the AGGREGATE section to reflect that??

[JohnTopley]

The ranges B17:E17, A17, F17 will need to be changed; can you supply the ranges where data is in your production file. Are there just columns as per your sample.

Perhaps post a file showing your production file format (no data needed).

[Exequiel3k]

It exceeds the limit -_-`:@ but i took a snapshot of the section..Attachment 461706Attachment 461707

[JohnTopley]

The system will not me view the attachment: I only wanted to know the ranges _ I don't need any data.

[Exequiel3k]

I've attached a file showing the range.., since rows will be infinite
(it really goes from A -> BC)

[JohnTopley]

Working from the image ...

this in AW5

=IF(MAX(AA5:AE5)<>0,INDEX($AW$5:$AW$50,AGGREGATE(14,6,ROW($A$5:$A5)*($AW$5:$AW5<>""),1)-4,1)-MAX(AA5:AE5),"")

Assumes data starts in row 5 and finishes at row 50. If it starts in 12 then change 5 to 12 and -4 to -11

the ranges in the image are totally out of sync.

[JohnTopley]

A more generic version

=IF(MAX(AA5:AE5)<>0,INDEX($AW$5:$AW$50,AGGREGATE(14,6,ROW($A$5:$A5)*($AW$5:$AW5<>""),1)-ROW($A$5)+1,1)-MAX(AA5:AE5),"")

will need to change 5 to start row

[Exequiel3k]

I can't get it to work...
I've striped the excel file so I can upload it. It's best this way :p

[Exequiel3k]

Alright, so it works though after 50rows it cuts.. Is there a way for it to never cut no matter the number of rows (ie infinit rows)???

Btw, thank you all for helping me

[JohnTopley]

Simply change the upper limit of the range ...5000 in the formula below (10000? 100000? ... choice is yours but keep it sensible)

=IF(MAX(AA5:AE5)<>0,INDEX($AW$5:$AW$5000,AGGREGATE(14,6,ROW($A$5:$A5)*($AW$5:$AW5<>""),1)-ROW($A$5)+1,1)-MAX(AA5:AE5),"")

[Exequiel3k]

Thx for the reply, what I did was just remove the "$" before it.. Do you think in the long-run that could be problimatic??

=IF(MAX(AA5:AE5)<>0,INDEX($AW$5:$AW$500,AGGREGATE(14,6,ROW($A$5:$A5)*($AW$5:$AW5<>""),1)-ROW($A$5)+1,1)-MAX(AA5:AE5),"")

[JohnTopley]

Should be OK.