IFSUM function

Posted by MichaelM on November 26, 2001 6:18 AM

I'm trying to use IFSUM on a column of numbers to the right of a column of dates, summing all those between a certain pair of dates. I have failed to get any combination of &LT; or > to reference cells containing the dates I want to sum between. Logically, I would have thought that AND(">A1","&LT;A2")as the argument within the IFSUM statement would work, but this only produces zero (without the "", it produces an error)

Any ideas??

Posted by MichaelM on November 26, 2001 6:20 AM

I seem to have been curtailed - the message should end -

Logically, I would have thought that AND(">A1","&LT;A2")as the argument within the IFSUM statement would work, but this only produces zero (without the "", it produces an error)

Any ideas??

Posted by MichaelM on November 26, 2001 6:26 AM

Of course I meant SUMIF

Dyslexia lures KO!

Posted by IML on November 26, 2001 6:49 AM

Surround your greater and less than signs with quotes and use an amperstand. For example
" > "&A1

(no need for the spaces around the signage).

luck good

Posted by MichaelM on November 26, 2001 7:27 AM

Nice try

Thanks for the speedy response - sadly it still produces zeros...

luck good - not unfortunately!

Posted by IML on November 26, 2001 8:08 AM

Re: Nice try

Sorry, I misunderstood. If your dates are in A1:A10, and what you want to sum is in B1:B1,
use
=SUMPRODUCT((A1:A10 > =D1)*(A1:A10 &LT; =D2)*(B1:B10))

This assumes your start date in D1 and end date in D2 and includes those dates. Alternatively,
you could use
=SUM(B1:B10)-SUMIF(A1:A10," &LT; "&D1,B1:B10)-SUMIF(A1:A10," > "&D2,B1:B10)

given the same assumptions

Posted by Aladin Akyurek on November 26, 2001 8:09 AM

Re: Nice try

========================

Michael --

Ian (IML) didn't see your post in integral (because it was scrambled), otherwise you'd get a reply like one that follows.

SUMIF is ordinarily used when summing on the basis of a single condition/criterion. With multiple conditions, you need array or SUMPRODUCT formulas. Since you have a between (dates) condition, you can still use SUMIF:

Lets say that you have the dates in E from E2 on, numbers in F from F2 on, and date conditions in A1 and A2 (A2 > A1).

=SUMIF(E2:E100,">"&A1,F2:F100)-SUMIF(E2:E100,">="&A2,F2:F100)

The expensive SUMPRODUCT equivalent would be:

=SUMPRODUCT((E2:E100>A1)*(E2:E100 &LT; A2),(F2:F100))

I'd suggest using the SUMIF version.

Cheers.

Aladin

Posted by iml on November 26, 2001 8:15 AM

i'd go w/ aladin's sumif as well (nt)