excel help needed

[chainsauce]

Hello I need help, I have this table for a attendance sheet. What I want is to count how many has a value for Late column for a specific Emp no. and the output would be to a N_late column

Emp no. Day Name Late
41 Day1 Name1 03:00
41 Day2 Name1
42 Day1 Name2 04:00
42 Day2 Name2 00:30
42 Day3 Name2
43 Day1 Name3


The output would be like this


Emp no. Day Name Late N_late
41 Day1 Name1 03:00 1
41 Day2 Name1 1
42 Day1 Name2 04:00 2
42 Day2 Name2 00:30 2
42 Day3 Name2 2
43 Day1 Name3 0



Thanks in advance

 

[Comfy]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Emp no.</td><td style=";">Day</td><td style=";">Name</td><td style=";">Late</td><td style=";">N_late</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">41</td><td style=";">Day1</td><td style=";">Name1</td><td style="text-align: right;;">03:00</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">41</td><td style=";">Day2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">42</td><td style=";">Day1</td><td style=";">Name2</td><td style="text-align: right;;">04:00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">42</td><td style=";">Day2</td><td style=";">Name2</td><td style="text-align: right;;">00:30</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">42</td><td style=";">Day3</td><td style=";">Name2</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">43</td><td style=";">Day1</td><td style=";">Name3</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C2,$D$2:$D$7,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C3,$D$2:$D$7,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C4,$D$2:$D$7,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C5,$D$2:$D$7,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C6,$D$2:$D$7,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$C$2:$C$7,C7,$D$2:$D$7,">0"</font>)</td></tr></tbody></table></td></tr></table><br />

 

[chainsauce]

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" rules="all" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" rules="all" width="100%" cellpadding="2.5px"><thead><tr style="background-color: rgb(224, 224, 240); 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 style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E2</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C2,$D$2:$D$7,">0")</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E3</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C3,$D$2:$D$7,">0")</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E4</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C4,$D$2:$D$7,">0")</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E5</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C5,$D$2:$D$7,">0")</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E6</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C6,$D$2:$D$7,">0")</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E7</th><td style="text-align: left;">=COUNTIFS($C$2:$C$7,C7,$D$2:$D$7,">0")</td></tr></tbody></table></td></tr></tbody></table>
This means that I should type it for every row in order to work. What if I have 1000 rows? How can I have for just one line only from 1 -1000 for example. THanks

 

[Comfy]

No, you type the first one into the cell and then copy the formula down.

One of the fastest ways would be to select E2 then while holding shift select E1000 (or what ever the last cell is)

Without selecting anything else type the formula for cell E2.

Once you have finished typing the formula press CTRL + ENTER

This will fill all other cells with this formula.

 

[chainsauce]

Help I'm getting 0's . I followed what you said.

 

[Comfy]

You values in column D are they TEXT?

If so that's why it isn't working.