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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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
48
Replies
6
Views
99
Replies
0
Views
39
Replies
1
Views
49
Replies
4
Views
31

1,109,408
Messages
5,528,603
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...