VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help on this one.

The first sheet on my excel, contains data for different dates as shown
below: (11 columns)
Date Trans subtype PG SSN, Time Taken, No of transc, verifyier,
time taken
19-jun Lu Rollover 525 999 2 4
SM 2
19-jun Lu Rollover 525 999 2 4
SM 2
20-jun Lu Rollover 525 999 2 4
SM 2
20-jun Lu Rollover 525 999 2 4
SM 2

Now, on my second sheet, I want a formula which will give me the total no of
tranc(transactions) done on 19-june & the total time taken to do those
transactions.?

I was trying the VLookup formula on my second sheet in this way.
say on b1 I enter the date 19-june, and below that under the heading of
TOTAL NO of Transactions, I enter this formula: =VLOOKUP(B1,DJ!A1:G19,7,0)
where DJ is the name of my first sheet & 7 is he seventh column from left.

1) How do I get the total no of transactions done on 19-june and the total
time taken to do them on that day? When I enter the date on b1 cell it should
sum up the total for that day.

2) How do I also get the total Time Taken to Verify those transactions done
on 19-june?

Please advise
 
Try:


No of Txs
=SUMPRODUCT(--(Sheet1!$A$2:$A$8=Sheet2!B1),(Sheet1!$G$2:$G$8))
Time fot TXs
=SUMPRODUCT(--(Sheet1!$A$2:$A$8=Sheet2!B1),(Sheet1!$F$2:$F$8))
Time to Verify
=SUMPRODUCT(--(Sheet1!$A$2:$A$8=Sheet2!B1),(Sheet1!$H$2:$H$8))

adjust ranges to suit

HTH
 
Back
Top