|

# » Totaling Two Ranges Using the SUMIF Formula

CATEGORY - Summing VERSION - All Microsoft Excel Versions
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

Rate This Tip
 1 2 3 4 5
Rating: 2.88     Views: 54756
Sum if between two dates
NIko  Posted on: 31-12-1969
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)
Reply: Sum if between two dates - NIko from San Francisco wrote on August 7, 2003 3:38 PM EST
Alan  Posted on: 31-12-1969
Hi NIko,

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!

Alan.
Thank
Cedric  Posted on: 31-12-1969
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 !
Alan  Posted on: 31-12-1969
Hi Cedric,

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?

Alan.
Niko  Posted on: 31-12-1969
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)
Reply: Thanks, but what about.... - Niko from San Francisco wrote on August 19, 2003 11:14 AM EST
Alan  Posted on: 31-12-1969
Hi Niko,

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?

Alan.
I get a value error
niko  Posted on: 31-12-1969
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.
Reply: I get a value error - niko from San Francisco wrote on August 20, 2003 11:11 AM EST
Alan  Posted on: 31-12-1969
Hi Niko,

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.

Alan.
Thanks. It worked.
Niko  Posted on: 31-12-1969
Thank a million Allan.
Name
Comment Title