SUMIF

Dropdown lists and tick boxes

Module 12 of 12 Spreadsheet in your browser

Hands-on drill What you'll do

Intro

Give a sheet a month picker and a column of tick boxes, then make a total react to them.

Data Validation → List makes a dropdown; Insert → Checkbox puts TRUE or FALSE in a cell for formulas to read.

Cell Controls

This lesson has you typing formulas, so it works best on a computer.

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How SUMIF works

What is a dropdown list?

A cell that only takes one of a few choices, picked from a ▾ list. Select the cell, then Data → Data Validation, Allow: List. It stops typing mistakes like "Mrach" before they break a formula.
Where do the choices come from?

The Source box. Type them with commas (Jan,Feb,Mar), or point at cells that hold them (=$H$2:$H$7). Cells are easier to change later: edit the list on the sheet and every dropdown follows.
How do I pick from it?

Click the ▾ beside the cell, or press Alt+↓ and use the arrow keys and Enter. Typing a value that isn't in the list is refused.
What is a tick box in a cell?

Insert → Checkbox (Excel 365) turns each selected cell into a box. Ticked, the cell holds TRUE; empty, FALSE. Press Space to tick or untick the selected cells.
How do formulas use tick boxes?

They read TRUE and FALSE like any value. =SUMIF(C2:C7,FALSE,B2:B7) adds the amounts not ticked yet; =COUNTIF(C2:C7,TRUE) counts the ticked ones. Tick a box and the total changes.
What about older Excel?

Older versions have tick boxes under Developer → Insert → Form Controls, linked to a cell one at a time. The cell checkbox does the same with no linking.

Know someone fighting with a spreadsheet? Send them this page.