Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can this be done with Excel?
Hi
I really need some help with a function that can do this: I have 5 seperate sheets in 1 workbook, each with a week of the month and a spare week. I.e. Week 1, Week 2, Week 3, Week 4, Week 5. Thus, in Week 1 I have a table that looks like this: A B C D E 1 Day Date Time Maureen John 2 Mon 01-Sep e-learning e-learning e-learning 3 08:00 - 11:00 Other Sales 4 11:00 - 14:00 Other Sales 5 14:00 - 17:00 Other Sales 6 Tue 02-Sep e-learning e-learning e-learning 7 08:00 - 11:00 Other Sales 8 11:00 - 14:00 Other Sales 14:00 - 17:00 Other Sales Cells A2:A5 are merged, as well as B2:B5. (This may impact the formula required). Cells A6:A8 and B6:B8 are also merged with the text horizontal. Sheets for weeks 2,3,4,5 are the same, they just have different dates. (i.e. instead of having 1 Sep & 2 Sep, they have 10 Sep & 11 Sep, etc.) What I want to do is count the number of times "John" does "Sales" after a specific date but before the next date. I.e. in this example how many times does "John" do "Sales" from the and including 2 Sep 08 but before 1 Oct 08? I.e. the answer needs to be 3. Firstly, how do i do this with one sheet? Secondly, how do I do the formula to do it across 5 different sheets? Any help would be appreciated! Many Thanks Alex |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can this be done with Excel?
Alex,
I don't think posting this over and over again is going to help you. My reason for avoiding the question is because of the merged cell issue and I'm still struggling to see a way around it. FWIW my view is merged cells are a pain and should be avoided, they only serve to make things look pretty and if you want pretty things you should use Powerpoint. However, back to the question. If you are prepared to junk the merged cell idea and create a table like this Day Date Time Maureen John Mon 01/09/2008 e-learning e-learning 01/09/2008 sales sales 01/09/2008 sales sales 01/09/2008 sales sales Tue 02/09/2008 e-learning e-learning 02/09/2008 sales sales 02/09/2008 sales sales 02/09/2008 sales sales This formula then works =SUMPRODUCT((B2:B13=DATE(2008,9,2))*(B2:B13<=DATE (2008,10,1))*(E2:E13="Sales")) You can put this on each sheet and sum the results of all of them in a seperate cell. Iv'e put dates in the formula but in practice I would prefer to use a cell reference with a date in. Mike "Alexander" wrote: Hi I really need some help with a function that can do this: I have 5 seperate sheets in 1 workbook, each with a week of the month and a spare week. I.e. Week 1, Week 2, Week 3, Week 4, Week 5. Thus, in Week 1 I have a table that looks like this: A B C D E 1 Day Date Time Maureen John 2 Mon 01-Sep e-learning e-learning e-learning 3 08:00 - 11:00 Other Sales 4 11:00 - 14:00 Other Sales 5 14:00 - 17:00 Other Sales 6 Tue 02-Sep e-learning e-learning e-learning 7 08:00 - 11:00 Other Sales 8 11:00 - 14:00 Other Sales 14:00 - 17:00 Other Sales Cells A2:A5 are merged, as well as B2:B5. (This may impact the formula required). Cells A6:A8 and B6:B8 are also merged with the text horizontal. Sheets for weeks 2,3,4,5 are the same, they just have different dates. (i.e. instead of having 1 Sep & 2 Sep, they have 10 Sep & 11 Sep, etc.) What I want to do is count the number of times "John" does "Sales" after a specific date but before the next date. I.e. in this example how many times does "John" do "Sales" from the and including 2 Sep 08 but before 1 Oct 08? I.e. the answer needs to be 3. Firstly, how do i do this with one sheet? Secondly, how do I do the formula to do it across 5 different sheets? Any help would be appreciated! Many Thanks Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|