Hello everyone
The below formula counts all the overtime hours every person does
during a given month, but what I would now like to do is count how many
sick days there are for example in the month of June 06. So in other
words I want to count "S" in the same period below ($J$6:$GY$6 for the
dates) and ($J$7:$GY$243 for the data base)

=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $243))

I hope someone can understad what I am trying to explain here and of
cause I hope someone can help me solve this problem.

Paul Maynard
Moscow
Russia


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

Hi!

=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $243))

I don't know what this portion is doing:

(Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)

Based on your explanation it calculates the date range and works so, all you
should need to do is change the 2nd array to:

=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $243="S"))

Biff

"paulrm906" wrote
in message ...

Hello everyone
The below formula counts all the overtime hours every person does
during a given month, but what I would now like to do is count how many
sick days there are for example in the month of June 06. So in other
words I want to count "S" in the same period below ($J$6:$GY$6 for the
dates) and ($J$7:$GY$243 for the data base)

=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $243))

I hope someone can understad what I am trying to explain here and of
cause I hope someone can help me solve this problem.

Paul Maynard
Moscow
Russia


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

Sorry Bif

I have tried your suggestion but can not get it work and sorry about
all that misleading information that I gave before but I have
re-adjusted your sugestion and it is now pasted below but maybe you can
see what I am doing wrong.

=SUMPRODUCT(('Sick,NS & PP WS'!$J$7:$GY$243-DAY('Sick,NS & PP
WS'!$J$6:$GY$6)+1=A25)*('Sick,NS & PP WS'!$J$7:$GY$243="S"))


Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

Ok.......

You need to start from the beginning.......

You want to count how many instances of "S" occur in a range based on a date
range, the month of June.

Where are the dates? What date format are they in? Where are the "S's"?

What is in A25?

Biff

"paulrm906" wrote
in message ...

Sorry Bif

I have tried your suggestion but can not get it work and sorry about
all that misleading information that I gave before but I have
re-adjusted your sugestion and it is now pasted below but maybe you can
see what I am doing wrong.

=SUMPRODUCT(('Sick,NS & PP WS'!$J$7:$GY$243-DAY('Sick,NS & PP
WS'!$J$6:$GY$6)+1=A25)*('Sick,NS & PP WS'!$J$7:$GY$243="S"))


Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

Hello Bif
Ok each question at a time;
1; Yes I want to count how many times the letter "S" appears for
example in the month of June.
2; The Dates are in column A
3; The A25 is the date where Jun-06 is.
4; The dates are formatted as below.
Jan-06
Feb-06
Mar-06
Apr-06
May-06
Jun-06

Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

Hello Bif

I got it working I think I must of been doing something very minor
because after I copied Bob's version over and then picked up on a small
error I managered to get it working so thanks to both you and Bob.

Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile: http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748

=SUMPRODUCT(('Sick,NS & PP WS'!$J$6:$GY$6-DAY('Sick,NS & PP
WS'!$J$6:$GY$6)+1=A25)*
('Sick,NS & PP WS'!$J$7:$GY$243="S"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"paulrm906" wrote
in message ...

Sorry Bif

I have tried your suggestion but can not get it work and sorry about
all that misleading information that I gave before but I have
re-adjusted your sugestion and it is now pasted below but maybe you can
see what I am doing wrong.

=SUMPRODUCT(('Sick,NS & PP WS'!$J$7:$GY$243-DAY('Sick,NS & PP
WS'!$J$6:$GY$6)+1=A25)*('Sick,NS & PP WS'!$J$7:$GY$243="S"))


Paul Maynard


--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
View this thread: http://www.excelforum.com/showthread...hreadid=550748