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

• 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. Jan 21, 2016 at 16:02
• 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.
– Kaz
Jan 21, 2016 at 16:05
• the term in the sum with AE2 is different from all the others it might be a bug Jan 21, 2016 at 16:09
• 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. Jan 21, 2016 at 16:11
• 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.
– Kaz
Jan 21, 2016 at 21:33

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({
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)
)
)