Thread: Counting formulae

I run office data for a warehouse based company and need to keep tabs on my staffs time owing figures. I am however come to a stumbling block over which functions to use to enable calculation. I hope you can help.

The fields are the following for the first part of the database:
Name
Staffing Grade - their job grade as a numerical value
Warehouse area - This is just for information
Date - date when hours were worked
Time taken/Time owing - the amount of time taken or extra time worked either as a positive or negative amount

In part 1 the persons name is listed along with the details of the extra work done in that period. They may have several different episodes of extra time worked or taken back.

I have a secondary part which has the following fields:
Name - a comprehensive list of the names of people in the company
Opening total - The total of time owing hours they have still from last month

hrs this month - *** The total of hours to be added/deducted from the above part 1 data *** This is the part which I need to calculate. How can I add together all hours data in part 1 just relating to an individual person then place it in part 2. Can a countif function somehow work?

Closing total - A total amount for the current month.

I really hope that someone can help on here as it will make my job a lot easier than working it all out on paper.

Thank you
Robert

You can use the SumIf function.

Rows/Columns A B
1 bob 1
2 sue 1
3 mike 1
4 bob 1
5 bob 1
6 sue 1

For the 'hours this month' calculation, using the table above as an example:

=SUMIF(A1:A6,E2,B1:B6)

This will count the hours (be it negative or positive) for the name selected. In my formula, E2 was the cell I choose to type the name i wanted the 'hours this month' for.

Should do the trick,

Thank you for your help. This formula worked perfectly!