Totaling Two Ranges Using the SUMIF Formula





To let the SUMIF formula add or subtract amounts from different ranges, insert two SUMIF formulas and combine them into a single formula:

1. Insert the SUMIF formula to total the amounts based on the criterion 701 into cell E2.
2. Insert the SUMIF formula to total the amounts based on the criterion 300 into cell E3.
3. Select cell E2 and copy the formula from the Formula Bar, select the formula and press Ctrl+C and click the Enter or Cancel symbol in the Formula Bar to exit Edit mode.
4. Select cell E5 and press Ctrl+V.
5. Select cell E3 and copy the formula from the Formula Bar without the equals (=) sign by selecting the formula and pressing Ctrl+C.
6. Select cell E5 and enter a minus (-) sign after the formula in the cell, and then press Ctrl+V.

The combined formula is now:
=SUMIF(A:A,D2,B:B)-SUMIF(A:A,D3,B:B).
Screenshot // Totaling Two Ranges Using the SUMIF Formula
Totaling Two Ranges Using the SUMIF Formula



9 thoughts on “Totaling Two Ranges Using the SUMIF Formula

  1. “I am trying to sum data in column B if date in Column G is > than today’s date. The formula below works only if your write in the date in the formula, but not if you provide a cell referrence in it (like G4, where G4 is 6/30/2003). Any ideas? Thanks, N

    =SUMIF(B:B,G4,D:D)-SUMIF(B:B,G4,E:E)”

  2. “I try to steer clear of SUMIF since it is part an AddIn and not a ‘standard’ Excel function. That means that if someone who has not installed the Analysis Toolpak (I believe) trys to open your spreadsheet, they will get #NAME errors since the SUMIF function will not be available.

    It is not really necessary anyway, since you can easily achieve the same thing with an array formula.

    Assuming you have dates in (G2:G7), and related data in (B2:B7) that you want to sum if the date is greater than a given entry:

    Enter the following array formula:

    =SUM((G2:G7>VALUE(“”31 July 2003″”))*B2:B7)

    Note that if the dates include a time element (non integers) then sometime during the day of 31 July 2003 will be after the date shown above. Therefore you may have to either clean your data or be very explicit about the time that you want to cut off.

    You can also use a reference to a date / time value rather than the expression (VALUE(“”31 July 2003″”)) I used above.

    To enter an array formula, type it as shown, then press Ctrl – Shift – Enter together (not just Enter) and Excel will put the braces around it.

    Check Excel help for more info on array formulae – very useful to learn! “

  3. Thank you so much for this trick about the array functions and this way to use the SUM function ! This is much more powerful than the sumif ! I could add the product of 2 ranges with a condition on a 3rd range !

  4. “SUMIF is just a dummed down form of an array formula.

    I suggest that you never use SUMIF. Partly because once you understand the power of array formulae, there is no need – it is too limiting.

    However, a better reason is that *when* someone else picks up your spreadsheet, and doesn’t have the right add-in installed, the SUMIF functions all fall to pieces. Better to use native Excel functions, or set them up as UDFs within the file in my opinion.

    BTW – Does anyone know whether SUMIF is still part of an add-in with the latest version(s) of Excel? “

  5. “what do you recommend in lieu of having to write out the date in the formula? Your solution works perfectly, but not if I refer to a cell as opposed to writing out the date (as you have below). Thanks again Alan.

    =SUM((G2:G7>VALUE(“”31 July 2003″”))*B2:B7)”

  6. “You can certainly replace that date (text), or the date value in the formula with a reference.

    For example, if you had a cell (A1) that contained the text “”31 July 2003″”, then the formula would read:

    {=SUM((G2:G7>VALUE(A1))*B2:B7)}

    If you had a cell (A2) that contained the value 37833 (the date value for 31 July 2003), then the formula would read:

    {=SUM((G2:G7>A2)*B2:B7)}

    Are they not working for you in some way? “

  7. “Thanks. I keep getting a #Value! unless I write the date in the formula. Referring to a cell, like A2 in yours, returns an error value.

    Still, thanks so much for teaching me to get around sumif.”

  8. “I would guess that you are entering the value in A2 in wrongly perhaps?

    If you do that, and use the forumula above, then do the following:

    1) Enter the formula / data (remember – array formula MUST be Shift-Ctrl-Enter)

    2) In the formula bar, highlight G2:G7 ONLY

    3) Press F9 and copy / paste whatever G2:G7 changes into back here.

    4) Press escape a few times to get back to a normal formula

    5) Highlight A2 or Value(A2) whichever you are using in the formula bar.

    6) Copy / paste that result here.

    We should be able to sort it out then. “

Leave a Reply

Your email address will not be published. Required fields are marked *


6 − four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>