colating multi rows of data into single rows - no to pivot tables!

[UKMAN]

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(INDIRECT(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

(e-mail address removed)

 

[Glenn]

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(INDIRECT(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

(e-mail address removed)

What is in B4, C4 and D1? And what does this have to do with the subject of
your post?

 

[Roger Govier]

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.

 

[Roger Govier]

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

 

[UKMAN]

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 (e-mail address removed) 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