Can this be done with Excel?

[Alexander]

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

 

[Mike H]

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 work
=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