Sheetsdrill

Level 4: Arrays and FILTER Exercise 69 of 92

Total overspend with ARRAYFORMULA

A Google Sheets formula exercise from Sheetsdrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.

The task

Some projects went over budget and some stayed under. Add up the overspend of the projects that went over (actual minus budget), ignoring the others. Do it in one formula in F1 with ARRAYFORMULA and IF, without a helper column.

What this exercise teaches

Inside ARRAYFORMULA, IF runs once per row and returns a column of results that SUM can add. Subtracting the two totals would let the projects under budget cancel out the ones over it.

A hint if you get stuck

IF(C2:C7>B2:B7, C2:C7-B2:B7, 0) gives the overspend or 0 for every row at once. Wrap it in SUM, then in ARRAYFORMULA.

Functions used: ARRAYFORMULA, IF, SUM

Try it now, free, no account needed

Your first 15 exercises on Sheetsdrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.

Open this exercise

More exercises in Arrays and FILTER