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

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
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

New Member
Joined
Mar 25, 2018
Messages
2
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

You can also try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2018</td><td style="text-align: right;;">2/1/2018</td><td style="text-align: right;;">3/1/2018</td><td style="text-align: right;;">4/1/2018</td><td style="text-align: right;;">5/1/2018</td><td style="text-align: right;;">6/1/2018</td><td style="text-align: right;;">7/1/2018</td><td style="text-align: right;;">8/1/2018</td><td style="text-align: right;;">9/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Manager 1</td><td style=";">Staff 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Manager 2</td><td style=";">Staff 2</td><td style=";">H</td><td style=";">H</td><td style=";">H</td><td style=";">H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Manager 1</td><td style=";">Staff 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Manager 4</td><td style=";">Staff 4</td><td style="text-align: right;;"></td><td style=";">SL</td><td style=";">SL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Manager 5</td><td style=";">Staff 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H</td><td style=";">H</td><td style=";">H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Manager 3</td><td style=";">Staff 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Manager 4</td><td style=";">Staff 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Manager 2</td><td style=";">Staff 8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Manager 1</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Manager 2</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Manager 3</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Manager 4</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Manager 5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Manager 1</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Manager 2</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Manager 3</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Manager 4</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Manager 5</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C11</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$2:$A$9=$A11</font>)*(<font color="Red">C$2:C$9="SL"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C17</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">$A$2:$A$9=$A17</font>)*(<font color="Green">C$2:C$9="SL"</font>)</font>)=0,"",SUMPRODUCT(<font color="Red">(<font color="Green">$A$2:$A$9=$A17</font>)*(<font color="Green">C$2:C$9="SL"</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top