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.
 

Some videos you may like

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
Joined
Sep 17, 2013
Messages
226
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
Joined
Oct 2, 2013
Messages
5
Thanks so much DavidCoop. I was heachade with this.
By the way, can you explain what -- mean in the formula?
 

DavidCoop

Board Regular
Joined
Sep 17, 2013
Messages
226
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
Joined
Oct 2, 2013
Messages
5

ADVERTISEMENT

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
Joined
Sep 17, 2013
Messages
226
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
Joined
Oct 2, 2013
Messages
5

ADVERTISEMENT

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.
 

DavidCoop

Board Regular
Joined
Sep 17, 2013
Messages
226
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top