Date wise calculation

[tqm1]

Ref: http://vbaexpress.com/forum/showthread.php?t=13360
posted there about 2 hours before.

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

[rylo]

Hi

Assuming that your data is in the range A1:D8

F1: 2/6/07 (the date)
F2: =SUMPRODUCT(--(A2:A8=F1),(C2:C8))
F3: =SUMPRODUCT(--(A2:A8=F1),--(C2:C8=0))
F4: =SUMPRODUCT(--(A2:A8=F1),(D2:D8))
F5: =SUMPRODUCT(--(A2:A8=F1),--(D2:D8=0))
F6: =SUMPRODUCT(--(A2:A8=F1))


HTH

rylo

[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

[rylo]

Adjust the range to suit your requirements.

rylo

[tqm1]

Is it possible to fullfil the purpose with OFFSET function?

[tqm1]

Dear Sir,

I applied your codes, but every variable returns=0

What is wrong?

Please see excel sheet in attachment.

[rylo]

Hi

Try

Please Login or Register  to view this content.

rylo

[vba-lover]

Dear All.

Your codes work fine for single DATE

Now I want to get result while using Date range

Date range is 02-06-07 to 03-06-07

How to use the reference of two dates in following codes

Dim F1 As Date, F2 As Integer, F3 As Integer, F4 As Integer 
 
    F1 = Sheets("weights").Range("B2").Value ' given date 
    
    F2 = Sheets("weights").Evaluate("SumProduct(--(B2:B100=" & CLng(F1) & "))") 'total Entries  

Please help

[vba-lover]

May it is not possible through VBA Codes?