where data (C5:G14), days (B5:B14), and codes (C4:G4) are named ranges. Note: In the latest version of Excel you can also use the FILTER function, as explained below.
SUMPRODUCT function
In the the example shown, the formula in J6 is: where data (C5:G14), days (B5:B14), and codes (C4:G4) are named ranges. In this case, we are multiplying all values in the named range data by two expressions that “zero out” values that should not be included in the final sum: The first expression applies a condition based on codes: Since J4 contains “B”, the expression creates an array of TRUE FALSE values like this: Each TRUE indicates a column where the code is “B”. The second expression tests for day: Since J4 contains “Wed”, the expression returns an array of TRUE and FALSE values like this: In this array, each TRUE represents a row where the day in column B is “Wed”. Next, the two arrays are multiplied together. In Excel, TRUE and FALSE values are automatically coerced to 1s and 0s by any math operation, so the multiplication step converts the arrays above to ones and zeros. When the horizontal array from the first expression is multiplied by the vertical array from the second expression, the result is a single two-dimensional array with the same dimensions as the original data: The array above is 10 rows by 5 columns.The semi-colons (;) indicate rows, and the commas (,) indicate columns. When this array is multiplied by data, the operation effectively “zeros out” the values in data that should not be included in the final sum. The process can be visualized as shown below, where the “Filter array” is the array of 1s and 0s above.
After data is multiplied by the Boolean array above, the result is a single array like this: With just one array to process, SUMPRODUCT returns the sum of all elements in the final array, 18.
FILTER function
In the latest version of Excel, you can also use the FILTER function to solve this problem. This formula works in two steps. First, the inner FILTER returns columns where code is “B”: The resulting array is returned to the outer FILTER function, which returns rows where day is “Wed”: The outer FILTER then returns matching data to the SUM function: The SUM function then calculates a sum and returns a final result, 18.
Notes
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.
