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.
Search JabSto ::

Custom Search