Countif or sumproduct & how to use it...

[yytsunamiyy]

Hi everyone,

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

 

[yytsunamiyy]

Nevermind everyone, I found Aladin's great explanation for sumproduct - Now I think I know what I am doing and how to get the needed info.

 

[Dave Patton]

Have you considered Pivot Tables?
See www.contextures.com and other sites for examples

 

[yytsunamiyy]

No, I haven't consideredPivot tables yet.
I was going the formula route because I need to do some additional calculations with those results and prepare some graphs. I haven't worked with pivot tables much, but I remember that there were some threads here dealing with problems concerning the furher use of data in pivots...

Plus, some of the calculations I am going to prepare data through vba first (sorting, putting it in tables and so on) and I think vba and pivottable sdon't mix all that well.

Maybe I am completely mistaken...