You're probably familiar with a table like the one above. What formula do you need to sum values in this table based on a specified condition?
You might use SUMIF:
=SUMIF($A$2:$A$17,H2,B2:B17)+SUMIF($C$2:$C$17,H2,$D$2:$D$17)+SUMIF($E$2:$E$17,H2,$F$2:$F$17)
(That is, Jan's total for A + Feb's total for A + Mar's total for A)
But if there are 12 months or more, the formula becomes very long and cumbersome to enter. What can you do?
The formula is:
=SUMIF($A$1:$E$17,H2,$B$1:$F$17)
=SUMIF($A$1:$E$17,H2,$B$1:$F$17)SUMIF's formula is =SUMIF(range_to_test, criteria, sum_range)corresponding rangethe range of numbers to be summed)
- The key point iscorresponding rangeThat is, Excel first checks whether A1:A17 equals H2 ("A"); if so it's TRUE, otherwise FALSE.
- For example, A2 (column 1, row 2) is 'A', so TRUE; A3 (column 1, row 3) is 'A', so TRUE; A5 (column 1, row 5) is 'C', so FALSE...
- Excel records which cells are TRUE or FALSE, then pulls the corresponding numbers from B1:E17 to sum. In other words, it sums B2, B3, B5, etc., which results in misaligned summation.
- Excel checks Column A, then Column B, up to Column E.
[Hide]
Join our 'Office Suite Real-Time Support' free Facebook group to learn more about Office.
View details
