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

[paulrm906]

Hello everyone
The below formula counts all the overtime hours every person doe
during a given month, but what I would now like to do is count how man
sick days there are for example in the month of June 06. So in othe
words I want to count "S" in the same period below ($J$6:$GY$6 for th
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 o
cause I hope someone can help me solve this problem.

Paul Maynard
Moscow
Russi

 

[Biff]

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]

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

 

[Biff]

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

 

[Bob Phillips]

=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]

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]

Hello Bif

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

Paul Maynar

 

[Bob Phillips]

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]

Thanks Bob

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

Again thanks very much.

Paul Maynar