Method of summing multiple columns in Excel tables

A method for summing multiple columns of conditions in an Excel table. First look at a group of sales data, is a shopping mall, different brands of TV three days sales record:

It is now necessary to calculate its three-day sales totals according to the G-List brand.

Must have a cousin already thought the method, since is by the condition sum, uses the SUMIF function to chant:

=SUMIF (B:B,G2,C:C) +sumif (b:b,g2,d:d) +sumif (b:b,g2,e:e)

The formula sums the conditions of columns C, D, and E, and then adds them.

There's no problem with that, but if you need to sum up 30 columns of data, that's too much of a hassle.

Well, there's a simpler formula:

Here we use the array formula:

=sum (IF (b$2:b$72=g2,c$2:e$72))

First, the IF function is processed, if the b2:b72 is equal to the G2 specified by the cell, returns the value of the corresponding row in the C2:e72 range, otherwise returns the logical value Flase, and the SUM function is used.

This is because the array operation is performed, so after the formula is edited, click the Edit bar, press Shift+ctrl, and then press ENTER. Note that the curly braces on both sides of the formula are automatically generated, and manual typing is not valid.

This formula is shorter, but need to press three keys to input, some new friends may not find the feeling, patience, try more times.

There is also a formula that does not require a three-key entry:

=sumproduct ((B$2:B$72=G2) *c$2:e$72)

First use B$2:B$72=G2 to determine whether B column equals the specified brand, get the logical value TRUE or flase, then multiply the logical value by the C2:E72 value, and finally use the sumproduct to compute the sum of the Chuyang product.