Sum matching columns and rows

In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is "A" and the day is "Wed". One way to solve this problem is with the SUMPRODUCT function, which can handle array operations natively, without requiring control shift enter. In the latest version of Excel, the FILTER function is another option. Both approaches are explained below.

SUMPRODUCT function

In the example shown, the formula in J6 is:

=SUMPRODUCT((codes=J4)*(days=J5)*data)

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:

(codes=J4)*(days=J5)

The first expression applies a condition based on codes:

(codes=J4)

Since J4 contains "B", the expression creates an array of TRUE FALSE values like this:

{FALSE,TRUE,FALSE,TRUE,FALSE}

Each TRUE indicates a column where the code is "B". The second expression tests for day:

(days=J5)

Since J4 contains "Wed", the expression returns an array of TRUE and FALSE values like this:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

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:

{0,0,0,0,0;0,0,0,0,0;0,1,0,1,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,1,0,1,0;0,0,0,0,0;0,0,0,0,0}

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:

=SUMPRODUCT({0,0,0,0,0;0,0,0,0,0;0,6,0,7,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,3,0,2,0;0,0,0,0,0;0,0,0,0,0})

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. 

=SUM(FILTER(FILTER(data,codes=J4,0),days=J5,0))

This formula works in two steps. First, the inner FILTER returns columns where code is "B":

FILTER(data,codes=J4,0) // filter columns

The resulting array is returned to the outer FILTER function, which returns rows where day is "Wed":

=SUM(FILTER(array,days=J5,0)) // filter rows

The outer FILTER then returns matching data to the SUM function:

=SUM({6,7;3,2}) // returns 18

The SUM function then calculates a sum and returns a final result, 18.

Notes

1. If you only need to sum matching columns (not rows) you can use a formula like this.
2. To sum matching rows only, you can use the SUMIFS function.