I m using excel 2003.
I have one excel sheet name is : ngp_fsr & second sheet name is : sheet1
In ngp_fsr sheet i have service persons record. i.e. Service person code, person name, time start, end time, duration, month&year etc etc.
Now i required in sheet1 :
How many days / hrs / minutes spent for total complaints during april - 2010 month.
Data from sheet "FSR_NGP", SE CODE in column "m" month&year in column "ad" & duration in column "W".
I have attach sample file for your ready reference.
Last edited by avk; 05-09-2010 at 03:09 PM.
You could use a formula like this in D5
=TEXT(INT(SUMPRODUCT((FSR_NGP!AE$2:AE$65000=$A5)*(FSR_NGP!AD$2:AD$65000=$C$4),FSR_NGP!W$2:W$65000)), "00 ")&TEXT(SUMPRODUCT((FSR_NGP!AE$2:AE$65000=$A5)*(FSR_NGP!AD$2:AD$65000=$C$4),FSR_NGP!W$2:W$65000),"hh :mm")
Note: SUMPRODUCT formula needs to be repeated here because of the format you require. If you can live with the result shown as decimal days you could use just the SUMPRODUCT part on its own, i.e. just
=SUMPRODUCT((FSR_NGP!AE$2:AE$65000=$A5)*(FSR_NGP!AD$2:AD$65000=$C$4),FSR_NGP!W$2:W$65000))
also you might want to reduce the number of rows in that (from 65000), unless you actually have that much data.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks