How do I put more than one criterion in a SUMIF function?
 

Need to have both "12/31/2004" and "<02/01/2005" as criterion in a sumif.

You use an array. Check out Chip Pearson's website....
http://www.cpearson.com/excel/array.htm
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Bryan Brassell" wrote:

Need to have both "12/31/2004" and "<02/01/2005" as criterion in a sumif.

This function will not work in my system. I am getting a #Value error. I
typed in the exact example from the Pearson page (the Phone/Smith example)
and it will not work. Do I need some sort of add-in? Text of my formula and
of example I loaded below:

=SUM((Sheet2!$A$261:$A$269<DATEVALUE("03/01/2005"))*(Sheet2!$A$261:$A$269DATEVALUE("01/31/2005"))
*Sheet2!H261:H2690)
=SUM((A2:A10="Phone")*(B2:B10="Smith")*C2:C10)

Either one gives me the same error.

Any ideas?
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141


"Gary Brown" wrote:

You use an array. Check out Chip Pearson's website....
http://www.cpearson.com/excel/array.htm
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Bryan Brassell" wrote:

Need to have both "12/31/2004" and "<02/01/2005" as criterion in a sumif.

On Tue, 31 May 2005 15:59:24 -0700, "Bryan Brassell" <Bryan
wrote:

Need to have both "12/31/2004" and "<02/01/2005" as criterion in a sumif.

=SUMIF(range, "" & DATE(2004,12,31), sum_range) -
SUMIF(range,"=" & DATE(2005,2,1), sum_range)

or (which seems slightly more clear to me, assuming you want data from Jan
2005):

=SUMIF(range, "=" & DATE(2005,1,1), sum_range) -
SUMIF(range, "" & DATE(2005,1,31), sum_range)


--ron

Did you miss the Ctrl-Shift-Enter part that makes the formula an array?
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Bryan Brassell" wrote:

This function will not work in my system. I am getting a #Value error. I
typed in the exact example from the Pearson page (the Phone/Smith example)
and it will not work. Do I need some sort of add-in? Text of my formula and
of example I loaded below:

=SUM((Sheet2!$A$261:$A$269<DATEVALUE("03/01/2005"))*(Sheet2!$A$261:$A$269DATEVALUE("01/31/2005"))
*Sheet2!H261:H2690)
=SUM((A2:A10="Phone")*(B2:B10="Smith")*C2:C10)

Either one gives me the same error.

Any ideas?
--
Regards,

Bryan Brassell
Padgett Business Services
281-897-9141


"Gary Brown" wrote:

You use an array. Check out Chip Pearson's website....
http://www.cpearson.com/excel/array.htm
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Bryan Brassell" wrote:

Need to have both "12/31/2004" and "<02/01/2005" as criterion in a sumif.