Skip to main content

Create a List Box That Determines Values to SUM in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

create-a-list-box-that-determines-values-to-sum-in-excel

A simple way to toggle between two different sum scenarios is by using the SUMIF function and IF function together with a list box. Here I will show you how to achieve this with a simple example.

Below there is data categorized as expense and non-expense. My goal is to create a list box that will allow me to choose from “expense” and “non-expense” sums of the data set below. The list box will trigger the IF statement to make a decision and the SUMIF function will sum the data.

create-a-list-box-that-determines-values-to-sum-in-excel

First, I'm going to select the cell where I want the list box to appear. Next, I select the data tab and select the data validation option.

create-a-list-box-that-determines-values-to-sum-in-excel

I chose the list validation and entered the only two options that I want to appear in my list box. After selecting OK, the list box is active.

create-a-list-box-that-determines-values-to-sum-in-excel

I entered a lengthy equation that I will break down into segments. Look at the formula below. The if statement is dependent on the list box (expense/non-expense). If expense is chosen the first option will display. If not, the second option will display. I did repeat the formula but you will see why when I go over the SUMIF part.

List box Yes print this option No print this option

=IF(A3="Expense", SUMIF(C4:C11,A3,D4:D11), SUMIF(C4:C11,A3,D4:D11))

In this part of the formula, I'm asking Excel to only sum D4:D11 for records that meet the same criteria as the list box. Meaning if expense is selected, only those records with values in column D will be added together. The duplicated formula is much like a placeholder for the IF function. It's the same formula but will yield a different result when the list box value changes.

SUMIF(C4:C11,A3,D4:D11)


create-a-list-box-that-determines-values-to-sum-in-excel

Here you can see the options that can be toggled from the list box.

create-a-list-box-that-determines-values-to-sum-in-excel

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder