SOLVED

# SUM(IF that changes when value in column A changes

Copper Contributor

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

best response confirmed by rbellotti (Copper Contributor)
Solution

# Re: SUM(IF that changes when value in column A changes

=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

# Re: SUM(IF that changes when value in column A changes

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