Forum Discussion

MikeBallard-1987's avatar
MikeBallard-1987
Copper Contributor
Jun 04, 2018

Repetitive Formulas

Is there a way to copy formulas accumulating totals without retyping each formula.  For example we need to total vendor by vendor month by month spending and accumulate those totals in a spreadsheet ...
Excel Desktop
Formulas & Functions
Damien_Rosario's avatar
Damien_Rosario
Silver Contributor
Jul 04, 2018

Hi MikeBallard-1987

 

As discussed offline, here's the attached workbook with the solution in it. I'm glad we worked it out.

 

=SUMPRODUCT(--(INDIRECT("'"&$E$4&"'!B4:B60")=$B4),INDIRECT("'"&$E$4&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$5&"'!B4:B60")=$B4),INDIRECT("'"&$E$5&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$6&"'!B4:B60")=$B4),INDIRECT("'"&$E$6&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$7&"'!B4:B60")=$B4),INDIRECT("'"&$E$7&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$8&"'!B4:B60")=$B4),INDIRECT("'"&$E$8&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$9&"'!B4:B60")=$B4),INDIRECT("'"&$E$9&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$10&"'!B4:B60")=$B4),INDIRECT("'"&$E$10&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$11&"'!B4:B60")=$B4),INDIRECT("'"&$E$11&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$12&"'!B4:B60")=$B4),INDIRECT("'"&$E$12&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$13&"'!B4:B60")=$B4),INDIRECT("'"&$E$13&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$14&"'!B4:B60")=$B4),INDIRECT("'"&$E$14&"'!D4:D60"))+SUMPRODUCT(--(INDIRECT("'"&$E$15&"'!B4:B60")=$B4),INDIRECT("'"&$E$15&"'!D4:D60"))

 

For formula lets us use a business name from the summary sheet (e.g. cell B4 is All seasons true value) and searches for a reference in the monthly sheets. When it finds it, it adds the running total so the sheet can adapt to when you make a transaction with that business name.

 

I'm sure this formula can be much improved by the experts here but in terms of working for you, glad that you have confirmed that it does!

 

Wishing you all the best.

 

Cheers

Damien 

John Jairo Vergara Domínguez's avatar
John Jairo Vergara Domínguez
Brass Contributor
Jul 05, 2018

Hi, to all!

 

Check this formula applied in workbook:

 

C4 : =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!B4:B42"),$B4,INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!D4:D42")))

 

D4 : =SUMPRODUCT(SUMIF(INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!B4:B42"),$B4,INDIRECT("'"&$E$4:$E$15&" "&RIGHT($D$1,2)&"'!E4:E42")))

 

Check file.  Blessings!

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Jul 05, 2018
    Thanks John! That is certainly less repetitive than my own attempt!

Resources