PDA

View Full Version : Date wise calculation



tqm1
06-14-2007, 07:32 PM
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--------50
03-06-07---400--------900--------50

I want to use following query against date=02-06-07
a=sum(weight2)
b=wight2=0
c=sum(cash)'100
d=cash=0
e=total records

Following results are required
a=900
b=1
c=100
d=1
e=3

Please help

geekgirlau
06-14-2007, 10:47 PM
Assuming the date 2/06/07 is in cell F1, your formulas would be:

a=SUMIF(A:A,F1,C:C)
b=SUMPRODUCT((A2:A8=F1)*(C2:C8=0)*1)
c=SUMIF(A:A,F1,D:D)

'I assume here you were after a count of all records for 2/06/07
'where Cash = 0
d=SUMPRODUCT((A2:A8=F1)*(D2:D8=0)*1)

'this formula assumes J1 contains the calculation for d
e=COUNTIF(A:A,F1)-J1

tqm1
06-14-2007, 11:37 PM
Dear Sir.

I want to get results form whole sheet1, not from range a1:d8
query should search given date from Firstrow to Lastrow

Please help again

mdmackillop
06-15-2007, 12:31 AM
Hi tqm,
We expect questioners to help themselves as well. GG's answer is a very good guide for you to work with. Have you tried adjusting this to suit your needs? We're happy to help with your issues if you get stuck.
FYI you cannot use SumProduct on a whole column, but you can insert values guaranteed to contain your data e.g. A2:A8000

tqm1
06-15-2007, 12:36 AM
Hi tqm,
you cannot use SumProduct on a whole column, but you can insert values guaranteed to contain your data e.g. A2:A8000

This is an other new thing which I learned.
Thanks