none
Please help me to minimize this formular in Excel 2013 RRS feed

  • Question

  • =SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$F$6:$F$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$G$6:$G$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$H$6:$H$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$I$6:$I$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$J$6:$J$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$K$6:$K$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$L$6:$L$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$M$6:$M$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$N$6:$N$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$O$6:$O$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$P$6:$P$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$Q$6:$Q$68,($A465),ORDER!$C$6:$C$68,AH$14)

    if not minimize, the size of 1 file can be more than 5MB (too big).

    Thanks for help

    Tuesday, January 19, 2016 2:26 AM

All replies

  • =SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$F$6:$F$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$G$6:$G$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$H$6:$H$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$I$6:$I$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$J$6:$J$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$K$6:$K$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$L$6:$L$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$M$6:$M$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$N$6:$N$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$O$6:$O$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$P$6:$P$68,($A465),ORDER!$C$6:$C$68,AH$14)+SUMIFS(ORDER!$E$6:$E$68,ORDER!$D$6:$D$68,$B465,ORDER!$Q$6:$Q$68,($A465),ORDER!$C$6:$C$68,AH$14) ....

    As you may see, there are many repeated formulas need to be shorten.

    Please help me


    Tuesday, January 19, 2016 3:39 AM
  • I would add an extra column or two for the repeated formulae. This also makes it much easier to check the formula is working properly. You can hide the extra columns.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Tuesday, January 19, 2016 10:01 PM
  • I would rename the ORDER sheet with a simple name as 'O'. I cannot see any other improvement, more than Rod Gill proposed, to use extra columns for these calculations. Also I can recommend you to use array formulas insetad of normal formulas.
    Arrays formula can made everything easier if you understand tha concept.
    I have made a very simple xls to make you understand the power of arrays.

    In the picture bellow is a normal calculation sum of some products and the price per one product

    But, if you'll work with array you'll reduce a lot of the length of formula:


    To use array formula, at the end of formula instead of pressing ENTER you need to press CTRL+SHIFT+ENTER.
    Two brackets will appear before and after the formula:



    I wish you succes in implementing all these formulas.


    Thanks in advance, Ciprian LUPU

    • Proposed as answer by Ciprian Lupu Thursday, January 21, 2016 6:00 PM
    Wednesday, January 20, 2016 6:52 AM
  • thank you
    Thursday, January 21, 2016 5:12 PM
  • Maybe...

    =SUM(IF(Order!$C$6:$C$68=AH$14,IF(Order!$D$6:$D$68=$B465,IF(Order!$F$6:$Q$68=$A465,Order!$E$6:$E$68))))

    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Saturday, January 23, 2016 3:33 PM