Forum Discussion

rbellotti's avatar
rbellotti
Copper Contributor
Jun 07, 2024
Solved

SUM(IF that changes when value in column A changes

I am trying to sum values in $AY and divide the total by $AT. Every time the value in column A changes, the group of rows being summed changes as well. I am manually updating the formula every 30 row...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 07, 2024

    rbellotti 

    =SUM(IF(($A$3:$A$15000=$E3)*($E$3:$E$15000=$A3)*($C$3:$C$15000=$C3),SUM(INDEX($AY:$AY,MATCH(A3,$A:$A,0)):INDEX($AY:$AY,ROW(A3)))/$AT3))

     

    Does this formula return the intended result? 

    I've changed for example

    $A:$A=$E3

    to

    $A$3:$A$15000=$E3

    because

    $A:$A

    references rows 1 to 1048576 in column A which is bad for the calculation performance.

     

    The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 07, 2024

rbellotti 

=SUM(IF(($A$3:$A$15000=$E3)*($E$3:$E$15000=$A3)*($C$3:$C$15000=$C3),SUM(INDEX($AY:$AY,MATCH(A3,$A:$A,0)):INDEX($AY:$AY,ROW(A3)))/$AT3))

 

Does this formula return the intended result? 

I've changed for example

$A:$A=$E3

to

$A$3:$A$15000=$E3

because

$A:$A

references rows 1 to 1048576 in column A which is bad for the calculation performance.

 

The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

Resources