SOLVED

SUM(IF that changes when value in column A changes

Copper Contributor

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.

1 Reply
best response confirmed by rbellotti (Copper Contributor)
Solution

@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.

1 best response

Accepted Solutions
best response confirmed by rbellotti (Copper Contributor)
Solution

@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.

View solution in original post