yytsunamiyy
Well-known Member
- Joined
- Mar 17, 2008
- Messages
- 963
Hi everyone,
I have the following table:
The complete table contains some 18720 records of individual, uniquly numbered bookings (Col. B) for the the years 2005 - 2008. There are no blank cells in the table.
Column G and I contain the German short names of the arrival / departure day, derived through the listed weekday function with the custom format "ddd".
What was easy for me was to find out the distribution of arrivals & departures by weekday for all records, giving me the following:
Column O is again formatted with custom format "ddd"
What I am trying to find out is the following:
a) What is the distribution of arrivals and departures per weekday for each year for 2005, 2006, 2007 and 2008?
b) How many arrivals / departures did I have per month - to be in a listed Apr. 2005 - March 2008. Ideally I would need the average number per day for each month, but I can do that easily with a helper column.
c) How many arrivals / departures were from PMV / CCS / RTR / ??? (Columns F & H) for the complete period, by year and by month.
I know I could find out what I want to know through filtering / copying and so on and could write the appropriate macros to do just that, but I feel reasonably certain that it is easier to find out what I need to using formulas. I was trying to write something like = COUNTIF(G:G,AND(D2<2006, O3)) to find out a) for 2005, but simply couldn't get the syntax right.
What I would really appreciate, if I have to use sumproduct, is if someone could explain to me how it works - Excel help is not terribly helpfull there and I have seen it used for similiar questions on the board before. I just don't seem to be able to get my head around it...
I am using a german language version of XL2007 on XP. Don't worry about the language, I should be able to translate the formulas from english to german correctly.
Thank you all,
Stephan
I have the following table:
Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Operator | Booking # | Pax | Startdate | Enddate | Arrival location | Arrival day | Departure location | Departure day | # of Products | Hotel count | RTR Count | Other Count | ||
2 | SALINA | 99535 | 2 | 01.01.2005 | 05.01.2005 | PMV | Sa | PMV | Mi | 1 | 1 | 0 | 0 | ||
3 | SALINA | 99682 | 2 | 02.01.2005 | 05.01.2005 | PMV | So | PMV | Mi | 1 | 1 | 0 | 0 | ||
4 | SALINA | 99625 | 1 | 05.01.2005 | 06.01.2005 | CCS | Mi | CCS | Do | 1 | 1 | 0 | 0 | ||
5 | MEIER | 97867 | 5 | 03.01.2005 | 08.01.2005 | PMV | Mo | PMV | Sa | 1 | 1 | 0 | 0 | ||
6 | MEIER | 99035 | 3 | 02.01.2005 | 10.01.2005 | PMV | So | RTR | Mo | 2 | 1 | 1 | 0 | ||
7 | MEIER | 99441 | 4 | 07.01.2005 | 10.01.2005 | RTR | Fr | RTR | Mo | 1 | 0 | 1 | 0 | ||
8 | SALINA | 99767 | 2 | 03.01.2005 | 11.01.2005 | PMV | Mo | PMV | Di | 1 | 1 | 0 | 0 | ||
9 | MEIER | 99571 | 2 | 04.01.2005 | 11.01.2005 | CCS | Di | RTR | Di | 2 | 1 | 1 | 0 | ||
Bookings |
The complete table contains some 18720 records of individual, uniquly numbered bookings (Col. B) for the the years 2005 - 2008. There are no blank cells in the table.
Column G and I contain the German short names of the arrival / departure day, derived through the listed weekday function with the custom format "ddd".
What was easy for me was to find out the distribution of arrivals & departures by weekday for all records, giving me the following:
Excel Workbook | |||||
---|---|---|---|---|---|
O | P | Q | |||
1 | Arr. Day | Dep. Day | |||
2 | So | 957 | 866 | ||
3 | Mo | 3337 | 3296 | ||
4 | Di | 697 | 1403 | ||
5 | Mi | 10522 | 9533 | ||
6 | Do | 433 | 492 | ||
7 | Fr | 2034 | 2505 | ||
8 | Sa | 740 | 625 | ||
9 | 18720 | 18720 | |||
Bookings |
Column O is again formatted with custom format "ddd"
What I am trying to find out is the following:
a) What is the distribution of arrivals and departures per weekday for each year for 2005, 2006, 2007 and 2008?
b) How many arrivals / departures did I have per month - to be in a listed Apr. 2005 - March 2008. Ideally I would need the average number per day for each month, but I can do that easily with a helper column.
c) How many arrivals / departures were from PMV / CCS / RTR / ??? (Columns F & H) for the complete period, by year and by month.
I know I could find out what I want to know through filtering / copying and so on and could write the appropriate macros to do just that, but I feel reasonably certain that it is easier to find out what I need to using formulas. I was trying to write something like = COUNTIF(G:G,AND(D2<2006, O3)) to find out a) for 2005, but simply couldn't get the syntax right.
What I would really appreciate, if I have to use sumproduct, is if someone could explain to me how it works - Excel help is not terribly helpfull there and I have seen it used for similiar questions on the board before. I just don't seem to be able to get my head around it...
I am using a german language version of XL2007 on XP. Don't worry about the language, I should be able to translate the formulas from english to german correctly.
Thank you all,
Stephan
Last edited: