| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| always display rows in Pivot tables (show items with no data) | Excel Discussion (Misc queries) | |||
| Multi rows to single row | Excel Discussion (Misc queries) | |||
| Putting data into pivot tables in columns not rows. | Excel Discussion (Misc queries) | |||
| Combining Pivot Tables - Summarising data from 100,000 rows | Excel Discussion (Misc queries) | |||
| Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Linear Mode
