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

=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

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

I corrected the ranges that you were using, you had the wrong range in
getting the start date, and one was too large.

--
HTH

Bob Phillips

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

"paulrm906" wrote
in message ...

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

Thanks Bob

Because I have been trying to sort this formula out for some time, but
sometimes I think I look at these formulas too much and after a while I
can not see a thing. As it is I spend the best part of 8 hours per day
in the office on the computer and in excel trying to sort out problems
with which I don't even know where to start most of the time. And it as
only been the last 6 mths since I started getting the help from experts
like yourself on these "Excel Forums" that I have progressed 100%. But
still miles behind many of you experts.

Again thanks very much.

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