Golden Dragoon
New Member
- Joined
- Mar 25, 2018
- Messages
- 2
Hi
I have found the forums here a great help in the past but never had to actually post anything for help, so I hope that someone will know a way to help me.
The issue I have is that we track holidays and sicknesses at my company on one sheet, this has the dates along the top row, the staff's team leader in the first column and the staff's name in the second column, there are other things calculating the total percentage of staff off and sickness for that day on other rows but that isn't relevant.
I have been asked to provide data on each team leaders sickness per day to be presented to the managers each week, as Excel 2003 lacks SUMIFS I have attempted to do this with SUMPRODUCT, but I am getting stuck as I can't seem to get it to work both horizontally and vertically to give me the figures I need, and using 2003 I can't think of another way of doing this.
I have thrown together a quick sample of what the sheet looks like:
So in this example I would need to provide a list of the managers with the dates, and the total number of SL for them on that date, so Manager 1 has 2 occurrences on 03/01/2018, and manager 4 has 1.
No doubt I will slap myself for being stupid when someone posts an easy solution to this, but I am drawing a blank.
Many thanks for your help![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I have found the forums here a great help in the past but never had to actually post anything for help, so I hope that someone will know a way to help me.
The issue I have is that we track holidays and sicknesses at my company on one sheet, this has the dates along the top row, the staff's team leader in the first column and the staff's name in the second column, there are other things calculating the total percentage of staff off and sickness for that day on other rows but that isn't relevant.
I have been asked to provide data on each team leaders sickness per day to be presented to the managers each week, as Excel 2003 lacks SUMIFS I have attempted to do this with SUMPRODUCT, but I am getting stuck as I can't seem to get it to work both horizontally and vertically to give me the figures I need, and using 2003 I can't think of another way of doing this.
I have thrown together a quick sample of what the sheet looks like:
Code:
[TABLE="width: 846"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]01/01/2018[/TD]
[TD="align: center"]02/01/2018[/TD]
[TD="align: center"]03/01/2018[/TD]
[TD="align: center"]04/01/2018[/TD]
[TD="align: center"]05/01/2018[/TD]
[TD="align: center"]06/01/2018[/TD]
[TD="align: center"]07/01/2018[/TD]
[TD="align: center"]08/01/2018[/TD]
[TD="align: center"]09/01/2018[/TD]
[/TR]
[TR]
[TD="align: center"]Manager 1[/TD]
[TD="align: center"]Staff 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 2[/TD]
[TD="align: center"]Staff 2[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 1[/TD]
[TD="align: center"]Staff 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 4[/TD]
[TD="align: center"]Staff 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 5[/TD]
[TD="align: center"]Staff 5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 3[/TD]
[TD="align: center"]Staff 6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 4[/TD]
[TD="align: center"]Staff 7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SL[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Manager 2[/TD]
[TD="align: center"]Staff 8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
So in this example I would need to provide a list of the managers with the dates, and the total number of SL for them on that date, so Manager 1 has 2 occurrences on 03/01/2018, and manager 4 has 1.
No doubt I will slap myself for being stupid when someone posts an easy solution to this, but I am drawing a blank.
Many thanks for your help