Help me! I want to count the working days of worker.

[tuyetngapt]

[TABLE="width: 757"]
<colgroup><col><col span="2"><col><col span="2"><col span="3"><col span="3"><col span="3"><col span="3"><col span="3"><col span="3"><col></colgroup><tbody>[TR]
[TD]Mon
[/TD]
[TD]Mon[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Tue[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Wed[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Thu[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Fri[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sat[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Sun[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Mon[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]2[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]5[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]


The workers works 2 shift: morning and afternoon, and they sometime work overtime at night (counted by hours).
1>I want to set up the formula to count the working days for the worker to count the normal working days (exclude overtime and Sunday)
{=COUNT(IF((A1:Y1<>"Sun"),A2:Y2,0))/2} (array formula)

The correct answer should be 7.5 but it turns out 5.

2>I count working days in sunday by {=COUNT(IF((A1:Y1="Sun"),A2:Y2,0))/2} ==>the answer is 11.5 while the correct one should be 1.

3>I want to add the overtime working by =SUMIF(A2:Y2,ISNUMBER(A2:Y2))/8 and it does not work also. (the right result should be 8h)

Can anybody help me to fix this??

Thanks alot.

 

[DavidCoop]

The formula you are trying to use won't give the desired result:

Try this:
1. =SUMPRODUCT(--($A$1:$Y$1<>"Sun")*($A$2:$Y$2="x"))/2
2. =SUMPRODUCT(--($A$1:$Y$1="Sun")*($A$2:$Y$2="x"))/2
3. =SUM(A2:Y2)

These formulae give the result you said you wanted. (With number 3, I don't understand why you're dividing by 8).

 

[tuyetngapt]

Thanks so much DavidCoop. I was heachade with this.
By the way, can you explain what -- mean in the formula?

 

[DavidCoop]

I'm not a mathematician, so my answer is possiblY over simplified, but the way I understand it is as follows:

In the SUMPRODUCT function, we are using BOOLEAN logic. By itself, this will produce a TRUE/FALSE answer. This needs to be converted to 1 for true or 0 for false. Placing the - sign before the calculation tells excel to perform some arithmetic. However, one minus will cause the answer to be -1 or 0. The double minus causes the result to be positive.

 

[tuyetngapt]

but i still do not understand why can we can use sumproduct in this case as i understand this function used for sum of multiply of 2 arrays, but we do not multiply here.

 

[DavidCoop]

SUMPRODUCT is an array formula, but you don't have to enter it using the SHIFT ENTER.

You are multiplying a group of 1's where row 1 does not equal "Sun" by another group of 1's where there is an x. Then SUMPRODUCT adds up the answers (product).

 

[tuyetngapt]

So you mean excel understand the text "MOn, tue", etc as 1 and x as 1 so that it can multiply. Tell me if i'm wrong, but i tried un example like this and the result is 0 while i expect it should be 3 (if it count text as 1's) . My formula is =SUMPRODUCT(A1:A3,B1:B3) the result is 0, and if i change to =SUMPRODUCT((A1:A3)*(B1:B3)) turns out #VALUE. (why u can use * in your sumproduct formula?)

[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]a
[/TD]
[TD="width: 64"]d[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]e[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your patient.

 

[DavidCoop]

Excel doesn't quite understand "Mon", "Tue" etc. as 1. The formula does, however answer TRUE to the BOOLEAN statement that "Mon" is NOT EQUAL (<>) to "Sun". These TRUE statements are converted to 1 by the double negative. In a similar way, the second half of the formula gets a 1 for each "x". You then effectively have two arrays of 1 and 0. {1,1,0,1,1,0....} * {1,1,1,1,1,1...} = 4.

In the example you gave, it doesn't work because a,b,c etc are not numeric. If you placed a number in each of the cells, BOTH formulas you used would give the same result.

I used the * to save two keystrokes. I could have used a comma, but would have to follow it with a "--" for the second BOOLEAN half of the formula.

Here are a couple of URL's which might help
Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online
CPearson.com Excel Newsletter 13-August-2007

 

[tuyetngapt]

Thank you. the information is very helpful for me