Ways to sum formulas in Excel

ways to sum formulas in Excel

1. Sum sum shortcut key

Set the sum sum formula in a table I want every Excel user to set up, so here's what you'll learn about the summation formula shortcut keys.

Requirements: Set the formula in the C5 cell shown in the following illustration.

Step: Select the C5 cell and press ALT + = To quickly set the sum formula.

2, skillfully set total formula

For small subtotal sum, is generally = Subtotal 1+ Subtotal 3 ... How many subtotal lines are added. In another way, the Total row = (all detail lines + small subtotal)/2, so the formula can be simplified to:

=sum (C2:C11)/2

3. Separating column summation

The columns are summed, typically in a table shown in the following illustration, that we can steal a lazy, and can sum it directly with SUMIF according to the title of Line 2nd. That

=SUMIF ($A $: $G $2,h$2,a3:g3)

If you don't have a title, you can only use a slightly more complex formula.

=sumproduct ((MOD (B3:G3, 2) =0) *b3:g3)

Or

{=sum (VLOOKUP (A3,a3:g3,row (1:3) *2,0))} array formula

4, single condition sum

According to the conditions of the classification of data is often encountered summation, if it is a single condition, the other functions do not have to consider, only use the SUMIF function on OK. (If you want to know more about the use of SUMIF functions, you can reply to SUMIF)

5, single condition fuzzy summation

If you need to make a fuzzy summation, you need to master the use of wildcard characters, where the asterisk is to represent any number of characters, such as "*a*" means that a before and after any number of characters, that is, contains A.