Prev: SUMIF with area that is not a range
Next: Reconstructing a table
From: UKMAN on 12 Mar 2010 11:11
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 UKMAN1(a)hotmail.com 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(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
> >>
> >> ukman1(a)hotmail.com
> >>
> >>
> .
>
 | 
Pages: 1
Prev: SUMIF with area that is not a range
Next: Reconstructing a table