3
\$\begingroup\$

I am making a sheet that adds up the total cost for a summer camp after registration. Each parent can register up to five children. I have already made my code and it works, but there must be a simpler way.

The code looks at the cell, and determines the cost of the program depending on whether the parent chooses after school, before school or both. The prices varies if it is a 4 day week or 5 day week (week 5 of 7 is a short week). The prices are called from another sheet. This process is repeated 35 times (the program is 7 weeks, plus 5 children can be registered).

Here is the code for one child:

=SUM(IF(AA2=Frais!$B$17;Frais!$C$9;IF(AA2=Frais!$B$18;Frais!$C$10;IF(AA2=Frais!$B$19;Frais!$C$11;0)));IF(AB2=Frais!$B$17;Frais!$C$9;IF(AB2=Frais!$B$18;Frais!$C$10;IF(AB2=Frais!$B$19;Frais!$C$11;0)));IF(AC2=Frais!$B$17;Frais!$C$9;IF(AC2=Frais!$B$18;Frais!$C$10;IF(AC2=Frais!$B$19;Frais!$C$11;0)));IF(AD2=Frais!$B$17;Frais!$C$9;IF(AD2=Frais!$B$18;Frais!$C$10;IF(AD2=Frais!$B$19;Frais!$C$11;0)));IF(AE2=Frais!$B$17;Frais!$D$9;IF(AE2=Frais!$B$18;Frais!$D$10;IF(AE2=Frais!$B$19;Frais!$D$11;0)));IF(AF2=Frais!$B$17;Frais!$C$9;IF(AF2=Frais!$B$18;Frais!$C$10;IF(AF2=Frais!$B$19;Frais!$C$11;0)));IF(AG2=Frais!$B$17;Frais!$C$9;IF(AG2=Frais!$B$18;Frais!$C$10;IF(AG2=Frais!$B$19;Frais!$C$11;0))))

This process is repeated 5 times.

Here is a link to the sheet (the cell with the formula is the last one).

Here are some pictures of the sheet:


The data prices are on a separate sheet:

enter image description here

\$\endgroup\$
  • 1
    \$\begingroup\$ Can't open the excel sheet here for security reasons, but the formula you posted suggests that a hidden column/row could unclutter this somewhat. \$\endgroup\$ – Sumurai8 Jan 21 '16 at 16:02
  • 1
    \$\begingroup\$ please include screenshots of the relevant spreadsheets (with data obfuscated if need be). It's really difficult to visualise (and hence offer improvements) your data otherwise. \$\endgroup\$ – Kaz Jan 21 '16 at 16:05
  • \$\begingroup\$ the term in the sum with AE2 is different from all the others it might be a bug \$\endgroup\$ – abuzittin gillifirca Jan 21 '16 at 16:09
  • \$\begingroup\$ abuzittin gillifirca, thanks for looking inot it. It's not a bug, in week five(AE2) the term is different because it is a short week. \$\endgroup\$ – Nicholas Chauvin nxchauvin Jan 21 '16 at 16:11
  • 1
    \$\begingroup\$ I appreciate the link but links can break. Documents can be moved. For the sake of future readers,it's best to embed content directly in the question. \$\endgroup\$ – Kaz Jan 21 '16 at 21:33
1
\$\begingroup\$

Short and partial answer

Instead of the use nested IF() use INDEX(), MATCH() and IFERROR()

Notes for a complete answer

It could be possible to do a further refinement of the formula by using arrays but the patterns should be understood deeply before to proceed on the implementation of such tools.

EDIT: An array formula was added to the bottom.

Explanation of the short and partial answer

The formula has 34 outer IF(), each of them:

  1. having a cell reference as the value to return when the test returns true. The three values are in the same one column range.
  2. having a nested IF() with another IF() as the value to return when the test returns false
  3. The last IF returns 0 in when the test returns false.

In cases like the above, using lookup functions will help to have concise formulas that also are usually easy to read and debug.

Formula pattern


IF(AA2=Frais!$B$17,Frais!$C$9,IF(AA2=Frais!$B$18,Frais!$C$10,IF(AA2=Frais!$B$19,Frais!$C$11,0)))

IFERROR(Index(Frais!$C$9:$C$11,Match(AA2,Frais!$B$17:$B$19,0)),0)

Resulting formula

Remark: Google Sheets allow the insertion of breaklines in formula. This could be helpful in complex formulas like the discussed in this Q&A.

=SUM(
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AA2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AB2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AC2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AD2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$D$9:$D$11, MATCH( AE2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AF2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( AG2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CC2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CD2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CE2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CF2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$D$9:$D$11, MATCH( CG2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CH2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( CI2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DI2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DJ2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DK2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DL2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$D$9:$D$11, MATCH( DM2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DN2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( DO2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( EO2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( EP2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( EQ2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( ER2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$D$9:$D$11, MATCH( ES2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( ET2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( EU2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( FU2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( FV2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( FW2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( FX2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$D$9:$D$11, MATCH( FY2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( FZ2, Frais!$B$17:$B$19, 0))),
    IFERROR(INDEX( Frais!$C$9:$C$11, MATCH( GA2, Frais!$B$17:$B$19, 0)))
)

Array formula

The following formula makes use of the array notation and capability of SUM() and VLOOKUP() to work with array formulas.

=ArrayFormula(
    SUM(
        IFERROR(
        VLOOKUP({
            AA2,AB2,AC2,AD2,AF2,AG2,
            CC2,CD2,CE2,CF2,CH2,CI2,
            DI2,DJ2,DK2,DL2,DN2,DO2,
            EO2,EP2,EQ2,ER2,ET2,EU2,
            FU2,FV2,FW2,FX2,FZ2,GA2
        },
        {Frais!$B$17:$B$19, Frais!$C$9:$C$11},2,0),0),
        IFERROR(
            VLOOKUP({
                AE2,
                CG2,
                DM2,
                ES2,
                FY2
            },
            {Frais!$B$17:$B$19,Frais!$D$9:$D$11},2,0),0)
    )
)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.