Forum Discussion
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 rows or so for a worksheet with over 11,000 rows. The examples below cover the first 93 rows, with two changes to the values in $A.
The following formula is in $BG. The values in column A are the same for rows 1 through 32 and I sum the values in $AY$3 through $AY32.
row 1 =SUM(IF($A:$A=$E3,IF($E:$E=$A3,IF($C:$C=$C3,SUM($AY$3:$AY3/$AT3,)))))
row 2 =SUM(IF($A:$A=$E4,IF($E:$E=$A4,IF($C:$C=$C4,SUM($AY$3:$AY4/$AT4,)))))
row 3 =SUM(IF($A:$A=$E5,IF($E:$E=$A5,IF($C:$C=$C5,SUM($AY$3:$AY5/$AT5,)))))
...
row 32 =SUM(IF($A:$A=$E32,IF($E:$E=$A32,IF($C:$C=$C32,SUM($AY$3:$AY32/$AT32,)))))
The value in column A changes from rows 33 through 64, so I modify the formula manually to sum the values in those rows:
row 33 =SUM(IF($A:$A=$E33,IF($E:$E=$A33,IF($C:$C=$C33,SUM($AY$33:$AY33/$AT33,)))))
row 34 =SUM(IF($A:$A=$E34,IF($E:$E=$A34,IF($C:$C=$C34,SUM($AY$33:$AY34/$AT34,)))))
row 35 =SUM(IF($A:$A=$E35,IF($E:$E=$A35,IF($C:$C=$C35,SUM($AY$33:$AY35/$AT35,)))))
...
row 64 =SUM(IF($A:$A=$E64,IF($E:$E=$A64,IF($C:$C=$C64,SUM($AY$33:$AY64/$AT64,)))))
The value in column A changes again from rows 65 through 93, so I sum those rows by manually changing the formula:
row 65 =SUM(IF($A:$A=$E65,IF($E:$E=$A65,IF($C:$C=$C65,SUM($AY$65:$AY65/$AT65,)))))
row 66 =SUM(IF($A:$A=$E66,IF($E:$E=$A66,IF($C:$C=$C66,SUM($AY$65:$AY66/$AT66,)))))
row 67 =SUM(IF($A:$A=$E67,IF($E:$E=$A67,IF($C:$C=$C67,SUM($AY$65:$AY67/$AT67,)))))
...
row 93 =SUM(IF($A:$A=$E93,IF($E:$E=$A93,IF($C:$C=$C93,SUM($AY$65:$AY93/$AT93,)))))
I'd like to modify the following formula so it doesn't require manually changing the first reference of $AY every the the value in $A changes. Worksheet attached. Thanks for your assistance.
=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.
1 Reply
=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.