This is simple enough to do when you only have a list of less than 10 items. Suppose you are dealing with large data sets with more than a thousand lines. It can get tedious to sum all the 'Total Balance' with the two above criteria.
Introducing SUMIF and SUMIFS Formulas
=SUMIFS(C3:C9,A3:A9,102015,B3:B9,5)
This formula above is telling Excel to sum the 'Total Balance' column (C3:C9) if both the 'GL' column (A3:A9) and 'Dimension' column (B3:B9) equal 102015 and 5 respectively. The total is 35.
However, suppose instead you want to sum everything EXCEPT 'GL' column with 102015 and 'Dimension' with 5. One way of doing this is by adding criteria after criteria in the above function. This would mean a longer excel formula. You can do this by adding the 'Criteria Range' and then the 'Criteria'.
=SUMIFS('Sum Range', 'Criteria Range1', Criteria1, Criteria Range2, Criteria2, Criteria Range3, Criteria 3,)
Formula to the Excluding Certain Criteria
=SUMIFS(C3:C9,A3:A9,"<> 102015",B3:B9,"<> 5")This formula will sum everything in the 'Total Balance' column that does not meet the criteria of a GL 102015 and Dimension 5. Alternatively to only exclude one criteria you can easily just enter in SUMIF(Range, Criteria, Sum Range) whereby the 'Range' is the column with the 'Criteria' you are looking to exclude and 'Sum Range' is the balances you want to sum.