Having issues calculating number of occurrences of sicknesses on a worksheet (Excel 2003)

[Golden Dragoon]

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:

[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

 

[dave2018]

Hi.

Create a new sheet (mine is named Sheet1) and create the fields as shown in this image:

https://drive.google.com/file/d/1245KQcGVuL_Tx4H9Zy9OWDeaTWWZnxQr/view

The formula in C2 is:
=SUMPRODUCT(--(INDEX(Sheet1!$D$2:$L$9,,MATCH(C$1,Sheet1!$D$1:$L$1,1))="SL")*(Sheet1!$A$2:$A$9=$B2))

you can then drag it accross and down.

Please correct me if i made a mistake, or if you want to make this even better!

 

[Golden Dragoon]

Thanks a lot, that worked really well and was similar to what I was trying previously but I was having issues with the index match so thought it wouldn't work. I am happy to be proven wrong on this one

 

[jtakw]

Hi,

You can also try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018	6/1/2018	7/1/2018	8/1/2018	9/1/2018
2	Manager 1	Staff 1			SL					SL
3	Manager 2	Staff 2	H	H	H	H
4	Manager 1	Staff 3			SL
5	Manager 4	Staff 4		SL	SL
6	Manager 5	Staff 5				H	H	H
7	Manager 3	Staff 6							SL
8	Manager 4	Staff 7								SL
9	Manager 2	Staff 8
10
11	Manager 1		0	0	2	0	0	0	0	1	0
12	Manager 2		0	0	0	0	0	0	0	0	0
13	Manager 3		0	0	0	0	0	0	1	0	0
14	Manager 4		0	1	1	0	0	0	0	1	0
15	Manager 5		0	0	0	0	0	0	0	0	0
16
17	Manager 1				2					1
18	Manager 2
19	Manager 3								1
20	Manager 4			1	1					1
21	Manager 5
Sheet8

Cell Formulas
Range	Formula
C11	=SUMPRODUCT(($A$2:$A$9=$A11)*(C$2:C$9="SL"))
C17	=IF(SUMPRODUCT(($A$2:$A$9=$A17)*(C$2:C$9="SL"))=0,"",SUMPRODUCT(($A$2:$A$9=$A17)*(C$2:C$9="SL")))

Use C17 formula instead of C11 formula if you don't want to see all those 0s.
Formulas copied down and across.