Roger,

thanks for input. I did try sending you part of the spreadsheet so you could
see the design etc but got a bounce back on your email address. :( My layout
does have seperate areas which your formula collates the data from simula to
what you suggest.

If you want to email my address I will send you a copy
which may make it easier.

In mean time I will see if I can use your new formula :)

many thanks as ever.

UKMAN

"Roger Govier" wrote:

My apologies, that formula should have been

=SUMPRODUCT((Sheet3!$A$2:$A$10=$A2)*(MONTH(B$1)=
MONTH(Sheet3!$D$1:$M$1))*Sheet3!$D$2:$M$10)

as column M would be December, not column O
--
Regards
Roger Govier

Roger Govier wrote:
Hi

You are not going to get your answer that way.
Continuing from the original layout and the original formula I gave you
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B2&":"&$C2)),
ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$ 1)),0)))
generate the table of data.

On a separate sheet (my data as above was on Sheet3), create a unique
list of names in A2 downward.
In B1:M1 enter dates for each month Jan through Dec
In B2 enter
=SUMPRODUCT((Sheet3!$A$2:$A$10=$A2)*(MONTH(B$1)=
MONTH(Sheet3!$D$1:$O$1))*Sheet3!$D$2:$O$10)

and this will give the totals by employee for each month.

Making the calculation work for only weekdays will take a little more
thought.
I will come back to you on this.
--
Regards
Roger Govier

UKMAN wrote:
the formula below allows me to state the number of days by month i.e.
10 days from 23rd April means 8 in April and 2 in May.
I have 2 issues with it though.

{=IF($H$16:$H$30=A4,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B4&":"&$C4)),ROW(INDI RECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))) ,)}

Issue 1:
H16:h30 is a list of names, A4 is the name of the student Using the
formula I can divide the dates over the months but for some reason
when I try to match a name (a4) against the list (h16:h30) I only ever
match the first name i.e. what is in h16??? other wise I get a "FALSE"
statement.

Issue 2: How can I amend the formula to only show the "working days"
in the return value i.e. 10 days from 23rd April means 6 in April and
4 in May.

many thanks




.