SUMIF

  • Thread starter Thread starter John
  • Start date Start date
J

John

I need to specify a date range in the criteria section of
a SUMIF Function - eg add net value of invoices if date is
between 01/01/04 and 31/01/04.

Additionally I would like to be able to add a second
criteria eg add net value of invoices if date is between
01-31 January 04 AND if the salesman was "Bill".

Any help gratefully received

Thanks
 
One way

=SUMPRODUCT(--(MONTH(A2:A20)=1),--(A2:A20<>""),--(B2:B20="Bill"),C2:C20)

where A holds the dates, B the sales people, and C the sales

Replace "Bill" with a cell (i.e. D2) that way you don't have to change the
formula, only the criteria cell where you type in the sales people
 
John,

Try

=SUMPRODUC((A1:A100>=DATE(2004,1,1))*(A1:A100<=DATE(2004,1,31))*(B1:B100="Bi
ll"))

will give the count. If you want to sum, use

=SUMPRODUC((A1:A100>=DATE(2004,1,1))*(A1:A100<=DATE(2004,1,31))*(B1:B100="Bi
ll"),C1:C100)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Try

=SUMPRODUC((A1:A100>=DATE(2004,1,1))*(A1:A100<=DATE(2004,1,31))
*(B1:B100="Bill"))

will give the count. If you want to sum, use

=SUMPRODUC((A1:A100>=DATE(2004,1,1))*(A1:A100<=DATE(2004,1,31))
*(B1:B100="Bill"),C1:C100)
Click to expand...

Copy & pase is a @#$%&!
 
Back
Top