Date wise calculation

[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--------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]

Assuming the date 2/06/07 is in cell F1, your formulas would be:

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

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

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

[tqm1]

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]

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]

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