Calculation in Date Range

[tqm1]

Dear Experts

Sheet1 has four columns from (column A : column D) and contains
following data

--date----weiht1----weight2------cash
01-06-07---500--------200--------50
01-06-07---300---------0--------150
02-06-07---200--------300--------50
02-06-07---100--------600---------0
02-06-07---800---------0---------50
03-06-07---600--------500--------30
03-06-07---400--------900--------50
04-06-07---400--------400--------20

I want to use following query between
Date range 02-06-07 to 03-06-07

a=sum(weight2)
b=count wight2=0
c=sum(cash)
d=count cash=0
e=total records

Following results are required
a=2300
b=1
c=180
d=1
e=5

Please hel

 

[Peo Sjoblom]

=SUMPRODUCT(--(Date_Range>=DATE(2007,06,02)),--(Date_Range<=DATE(2007,06,03)),Weight2_range)

to sum the weight, replace Weight2_range with Cash_range to sum the cash

How do you want to count the weight and the cash? Nor do I understand what
you mean by total records

I am assuming that you by 02-06-07 mean June 2nd 2007 and not January 1st
2007, if so change the month and day part in the DATE function



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)