Part II: Working with Formulas and Functions
way to use multiple conditions. When you want to test different ranges, the formulas get
tricky because you have to make sure you don’t double count values.
Figure 13.12 shows a list of dates and amounts. We want to find the sum of the values that
are between June 23 and June 29, inclusive. The starting and ending dates will be put in
cells F4 and F5, respectively.
=SUMIF(B3:B20,"<="&F5,C3:C20)-SUMIF(B3:B20,"<"&F4,C3:C20)
FIGURE 13.12
Summing values that are between two dates
This technique subtracts one SUMIF from another to get the desired result. The first
SUMIF, SUMIF(B3:B20,"<="&F5,C3:C20), returns the sum of the values less than or
equal to the date in F5, June 29 in this example. The conditional argument is the less-than-
or-equal-to operator concatenated to the cell reference F5. If that was the whole formula,
the result would be 5,962.33. But we want only values that are also greater than or equal
to June 23. That means we want to exclude values that are less than June 23. The second
SUMIF achieves that. Sum everything less than or equal to the later date, and subtract
everything less than the earlier date to get the sum of values between the two dates.
Using SUMIFS
You may even find SUMIFS to be more intuitive than the subtraction technique. The for-
mula =SUMIFS(C3:C20,B3:B20,"<="&F5,B3:B20,">="&F4) sums the values in C3:C20
that correspond to the values in B3:B20 that meet the criteria pairs. The first criteria pair is
