» Totaling Two Ranges Using the SUMIF Formula

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)

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 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 !

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.

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)

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.

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.

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.

Thank a million Allan.