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

tuyetngapt

New Member
Joined
Oct 2, 2013
Messages
5
Mon
MonMonTueTueTueWedWedWedThuThuThuFriFriFriSatSatSatSunSunSunMonMonMonTue
xx2xx5xx1xx x xx xx xxxx

<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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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).
 
Upvote 0
Thanks so much DavidCoop. I was heachade with this.
By the way, can you explain what -- mean in the formula?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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
be
cf
0

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

Thanks for your patient.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top