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.

​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.  Read More

​