If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
colating multi rows of data into single rows - no to pivot tables!
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 |
#2
|
|||
|
|||
colating multi rows of data into single rows - no to pivot tables!
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 What is in B4, C4 and D1? And what does this have to do with the subject of your post? |
#3
|
|||
|
|||
colating multi rows of data into single rows - no to pivot tables!
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 |
#4
|
|||
|
|||
colating multi rows of data into single rows - no to pivot tables!
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 |
Thread Tools | |
Display Modes | |
|
|