Microsoft Office Tutorials and References

In Depth Information

**Summing Things Up**

The result you see is a sum of the numeric values where the items in the first

range matched the selected criteria.

The example in Figure 8-8 sums values when the store is Great Grocery but

does not use the date in the calculation. What if we need to know how much

was spent at Great Grocery in April only? Excel provides a function for this of

course — SUMIFS.

SUMIFS lets you apply multiple “if” conditions to a sum. The format of SUMIFS

is a bit different than SUMIF. SUMIFS uses this structure:

=SUMIFS(range to be summed, criteria range 1, criteria 1,

criteria range 2, criteria 2)

The structure requires the range of numerical values to be entered first,

followed by pairs of criteria ranges and the criteria itself. In Figure 8-9 the

formula is:

=SUMIFS(B3:B15,A3:A15,”<5/1/2013”,C3:C15,”Great Grocery”)

The Function uses B3:B15 as the source of values to sum. A1:A15 is the first

criteria range, and “<5/1/2013” is the criteria. This tells the function to look for

any date that is earlier than May 1, 2013 (which filters the dates to just April).

This is followed by a second criteria range and value — in C3:C15 look just for

Great Grocery. The final sum of $84.24 adds just three numbers — 15.04, 42.25,

and 26.95 — because these are the only values in April for Great Grocery.

Figure 8-9:

Using

SUMIFS to

get a

multiple

filtered sum.