Multi add, in array of data

[Paul]

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

 

[Roger Govier]

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
Data>Pivot Table>Finish

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 date>Group and Outline>Group>click Month And
Day>OK
Right click on any cell within the PT>Pivot Table Wizard>Layout>Drag Month
to the Page area>OK>Finish
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

 

[Paul]

Hi Roger

Thanks for the suggestion.... I will have a go....
I wonder: could we chat on the phone:
My email is (e-mail address removed).... I would be happy to call you to
discuss this issue