Do you ever get an answer to this? You can create a second sheet (sheet2)
which looks up all your data from the first sheet (sheet1), as follows:

First create a second sheet with the following headers in columns A-D:

Date 9:00 12:00 15:00

In column A you'll need to fill in all the dates from your original sheet
(each date needs to appear only once). Should be easy if they are
continuous, or you can use the Advanced Filter to get a list of all unique
values, then paste that list in under the Date header.

Now in cell B2 paste the following formula:

=SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*(Sheet1!$B$1 :$B$1000=B$1)*(Sheet1!$C$1:$C$1000))

You'll need to change the sheet references if yours are renamed, and if your
sheet1 has more than 1000 rows then bump up those limits in the formula. But
don't alter the $ signs, they are placed to freeze some of the ranges and
allow others to flex when copied. After customizing your formula in B2, you
can copy it across to C2 and D2, verify that it is working, then copy that
set of three formulas down to all your date rows.

Hope that helps (albeit late).



"netfan" wrote:


Dear andy,
thanks for ur reply. as you imagined, it's a huge file. so i have to
find some way to do with it. any more suggestion pls?


--
netfan
------------------------------------------------------------------------
netfan's Profile: http://www.excelforum.com/member.php...o&userid=31349
View this thread: http://www.excelforum.com/showthread...hreadid=556167