Forum Discussion
Wrong formula recalculation on dynamic table
Hi, I have a table with 3 columns (excel 2013). The last column has a formula that computes sum of "value" column from previous rows with same "product" name.
This tables auto expand, but formula is inserted with wrong cell reference.
When I choose to fix the formula with contestual menu references are fixed.
In attached sample I had this formula on K10 (also the 'english' version on second line)
=SOMMA.SE($I$4:$I10;[@Product];$J$4:$J10)
=SUMIF($I$4:$I10,[@Product],$J$4:$J10)
when I had a new row (apple with value 1)
formula on K10 became
=SOMMA.SE($I$4:$I11;[@Product];$J$4:$J11)
=SUMIF($I$4:$I11,[@Product],$J$4:$J11)
same as the one on new inserted line (K11).
How can I stop this behavior?
One possibility is to give up the practice of direct cell referencing entirely and use structured references throughout.
= SUMIF( INDEX([Product],1):[@Product], [@Product], INDEX([value],1):[@value] )
2 Replies
One possibility is to give up the practice of direct cell referencing entirely and use structured references throughout.
= SUMIF( INDEX([Product],1):[@Product], [@Product], INDEX([value],1):[@value] )Thanks PeterBartholomew1 it works perfectly!
Italian translation for your formula
=SOMMA.SE(INDICE([Product];1):[@Product];[@Product];INDICE([value];1):[@value])
