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

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:
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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
view
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!
 
Upvote 0
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 :)
 
Upvote 0
Hi,

You can also try this:


Book1
ABCDEFGHIJK
11/1/20182/1/20183/1/20184/1/20185/1/20186/1/20187/1/20188/1/20189/1/2018
2Manager 1Staff 1SLSL
3Manager 2Staff 2HHHH
4Manager 1Staff 3SL
5Manager 4Staff 4SLSL
6Manager 5Staff 5HHH
7Manager 3Staff 6SL
8Manager 4Staff 7SL
9Manager 2Staff 8
10
11Manager 1002000010
12Manager 2000000000
13Manager 3000000100
14Manager 4011000010
15Manager 5000000000
16
17Manager 1 21
18Manager 2
19Manager 31
20Manager 4111
21Manager 5
Sheet8
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top