Summary

To enable a dropdown with an "all" option you can use data validation for the dropdown list, and a formula based on IF, SUM, and SUMIF functions to calculate a conditional sum. In the example shown the formula in G5 is:

=IF(F5="all",SUM(qty),SUMIF(color,F5,qty))

where "color" (C5:C15) and "qty" (D5:D15) are named ranges.

Example

When F5 is selected, the following dropdown appears:

Dropdown sum with all option in action

When the user makes a selection, the correct sum is returned.

Generic formula

=IF(F5="all",SUM(D:D),SUMIF(C:C,A1,D:D))

Explanation 

The dropdown is set up with a simple data validation rule based on a "list":

Red,Blue,Green,All

The named ranges "color" (C5:C15) and "qty" (D5:D15) are for convenience only.

The formula in G5 performs a conditional sum based on the current dropdown selection in F5. The outermost function is an IF statement, which checks if the selection is "all":

=IF(F5="all",SUM(qty)

If so, the formula returns the sum of quantity column as a final result.

If F5 is any value except "all" (i.e. "red", "blue", or "green"), the logical test returns FALSE and IF routes the formula to the SUMIF function:

SUMIF(color,F5,qty)

SUMIF calculates a conditional sum based on the value in F5 and returns the result.

Related formulas

Excel formula: Sum if cells are equal to

Sum if cells are equal to

In this example the goal is to sum the numbers in the range F5:F16 when cells in the range C5:C15 contain "Red". To solve this problem, you can use either the SUMIFS function or the SUMIF function . The SUMIF function is an older function that supports only a single condition. SUMIFS on the other...

Related functions

Excel SUMIF function

SUMIF Function

The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching....
Excel SUM function

SUM Function

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Excel IF function

IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions