Google Sheets: Sum If Checked (2 Ways)

Adding values under certain conditions is a common affair in Google Sheets. But one approach that can be used to simplify a condition is the checkbox. As such, today we will look at how we can sum values if checked in Google Sheets.

Let’s get started.

2 Ways to Sum Values If Checked in Google Sheets

1. Using the SUMIF Function

For our first method, we look to the SUMIF function. The function is highly intuitive for this problem since it does exactly what we want: sum according to a condition.

The SUMIF function syntax:

SUMIF(range, criterion, [sum_range])

We will utilize all three of the fields for the following dataset to calculate the total per the checked Delivered boxes:

dataset for sum if checked in google sheets

Let’s go through the process step-by-step.

Step 1: Open the SUMIF function in the target cell and input the criterion range. To future-proof the formula, we have removed the column range limitation. We use D3:D instead of D3:D5.

inputting the criterion range to the sumif function

Step 2: Add the criterion. A checked checkbox means that its value is TRUE. Thus, our criterion will be “TRUE”.

inputting the criterion for the sumif function

Step 3: Input the sum range. This will be our Price column. Same as the criterion range, we are keeping the sum range open-ended, C3:C.

inputting the sum range for the sumif function in google sheets

Step 4: Close parentheses and press ENTER.


using the sumif function in google sheets to sum if checked

Note: We have applied conditional formatting to our dataset to highlight our choices. To know more about how we achieved this, please see our Conditional Formatting with Checkbox in Google Sheets article.

2. Using the SUMPRODUCT Function

Our next method revolves around the SUMPRODUCT function. Since the function is versatile, it can easily mimic the idea and functionality of the SUMIF function.

The SUMPRODUCT function syntax:

SUMPRODUCT(array1, [array2, ...])

sumproduct function syntax

Step 1: We start by opening our SUMPRODUCT function in the target cell and adding the column range of Price. We will keep the range open-ended, C3:C.

inputting the price range in the sumproduct function

Step 2: Input the AND operator or the asterisk (*) symbol.

applying the and operator

What this does is that it applies the AND logic to the statement to check whether both cells in each row have positive values, aka TRUE:

  • The Price column will always be TRUE since it contains a numerical value.
  • The checkbox only returns TRUE when it is checked.

So, when the checkbox is TRUE, the function will return the Price to the calculation as it satisfies a positive AND logic (TRUE and TRUE).

Step 3: Input the range of the Delivered column. This too shall be open-ended, D3:D.

inputting the range of the delivered column

Step 4: Close parentheses and press ENTER.

using the sumproduct function in google sheets to sum if checked

Final Words

That concludes all the ways we can use to sum values depending on checked conditions in Google Sheets. While the SUMIF function may be more intuitive and a simpler solution to this problem, the SUMPRODUCT function can prove to be more worthwhile to know for different scenarios.

Please feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply