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

[Bryan Brassell]

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

 

[Gary Brown]

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

 

[Bryan Brassell]

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$269>DATEVALUE("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

 

[Ron Rosenfeld]

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

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

 

[Gary Brown]

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