Summer camp cost calculation using Google Sheets

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:

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:

• having a cell reference as the value to return when the test returns true. The three values are in the same one column range.

• having a nested IF() with another IF() as the value to return when the test returns false

• 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)
    )
)

StackExchange Code Review Q#117507, answer score: 2