Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi add, in array of data
I am currently writing a Visit sheet for my staff to use. They have 4 areas
of work that they cover, referred to as B, E, L, P. They have to log the date, Time of departure, Time arrived at a job and the respective mileages. Each visit sheet may contain a number of visits for the various areas. There may also be more than one set of visits carried out in that complete day. The purpose of the data collection is to split the time & mileage spent between the areas of work covered and charged back accordingly. Following all the data being input by the staff The Formula I am trying to create needs to: Search the column containing the dates and add together all the mileage used fields in respect of each the specific Discipline 'i.e. B, or E or L or P' So in English....for example.... Date: Mileage leaving Office: Discipline: Mileage Arrived: Mileage Returning Office 01/03/08: 012000 : E : 012025 : No Mileage as not returned to office (used mileage = 25) 01/03/08: No Mileage : B : 012031 : No mileage to office (used mileage = 6) 01/03/08: No Mileage : E : 012045 : No Mileage to Office (used mileage = 14) 01/03/08: No Mileage : P : 012047 : 012051 (used mileage = 6) 03/03/08: 012075 : E : 012077 : 012079 (used mileage = 4) The above is a rough sample of what data has been collected...... I need to select the whole of column A (which contains the date visited) and search for all dates (for example) that contain 01/03/08 and add together the figures that correspond to 'B' then a separate for 'E' etc..... this filtering needs to result in: Date B: E: L: P: 01/03/08 6 39 0 6 03/03/08 0 4 0 0 and so on for each date a visit was made in that month.... Hope this make sense |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi add, in array of data
Hi Paul
I am assuming the data is in columns A to Column E and that column F contains the calculated mileage as a figure ( not with text as you have shown for illustration). If that is the case, then the easiest solution would be a pivot table. Give column F the header title of Mileage Used Place your cursor in cell A1 DataPivot TableFinish In the PT skeleton that appears on the new sheet Drag Date to the Row area Drag Discipline to the Column area Drag Mileage Used to the Data area Double click on the first dateGroup and OutlineGroupclick Month And DayOK Right click on any cell within the PTPivot Table WizardLayoutDrag Month to the Page areaOKFinish Now use the dropdown on Month to Select March Alternatively if you don't want to use PT, then you could use formulae. In H1 Enter B, I1 enter E, J1 enter L and K1 enter P In G2 enter 01 Mar 08 In G3 enter =G2+1 Copy down till you have all dates for March showing in column G In H2 enter =SUMPRODUCT(($A$2:$A$1000=$G2)*($C$2:$C$1000=H$1)* $F2:$F1000) Copy across through I2:K2 Copy H2:K2 down through H3:H33 -- Regards Roger Govier "Paul" wrote in message ... I am currently writing a Visit sheet for my staff to use. They have 4 areas of work that they cover, referred to as B, E, L, P. They have to log the date, Time of departure, Time arrived at a job and the respective mileages. Each visit sheet may contain a number of visits for the various areas. There may also be more than one set of visits carried out in that complete day. The purpose of the data collection is to split the time & mileage spent between the areas of work covered and charged back accordingly. Following all the data being input by the staff The Formula I am trying to create needs to: Search the column containing the dates and add together all the mileage used fields in respect of each the specific Discipline 'i.e. B, or E or L or P' So in English....for example.... Date: Mileage leaving Office: Discipline: Mileage Arrived: Mileage Returning Office 01/03/08: 012000 : E : 012025 : No Mileage as not returned to office (used mileage = 25) 01/03/08: No Mileage : B : 012031 : No mileage to office (used mileage = 6) 01/03/08: No Mileage : E : 012045 : No Mileage to Office (used mileage = 14) 01/03/08: No Mileage : P : 012047 : 012051 (used mileage = 6) 03/03/08: 012075 : E : 012077 : 012079 (used mileage = 4) The above is a rough sample of what data has been collected...... I need to select the whole of column A (which contains the date visited) and search for all dates (for example) that contain 01/03/08 and add together the figures that correspond to 'B' then a separate for 'E' etc..... this filtering needs to result in: Date B: E: L: P: 01/03/08 6 39 0 6 03/03/08 0 4 0 0 and so on for each date a visit was made in that month.... Hope this make sense |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi add, in array of data
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi-Cell Array Formula | Excel Worksheet Functions | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
newbie question on multi-dimensional array | New Users to Excel | |||
how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) |