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

#### tuyetngapt

##### New Member
 Mon Mon Mon Tue Tue Tue Wed Wed Wed Thu Thu Thu Fri Fri Fri Sat Sat Sat Sun Sun Sun Mon Mon Mon Tue x x 2 x x 5 x x 1 x x x x x x x x x x x

<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>
</tbody>

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.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### DavidCoop

##### Board Regular
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

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

#### DavidCoop

##### Board Regular
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

##### New Member

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

##### Board Regular
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

##### New Member

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

 a d b e c f 0

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

#### DavidCoop

##### Board Regular
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

#### tuyetngapt

##### New Member
Thank you. the information is very helpful for me

Replies
3
Views
65
Replies
6
Views
105
Replies
0
Views
89
Replies
1
Views
69
Replies
9
Views
118