count confusion - please help!

[Sezebear]

Hi,

I am trying to do a summary page for an existing hotel bookings spreadsheet. I am trying to do a number of different things which is using more brainpower than I have.

1. I have a list of usual guest names all being counted using a countif formula, =COUNTIF('2016'!B:B,"=Guest A") easy enough, however I need to count the 'other' guests but as these are not a set list of data but rather a count if not one of the usual guest's I have no idea how to do this.

2. I want to see how many nights each guest has stayed in total so example guest A has 9 bookings over the year these are spread throughout the data as it is in chronological order but some might be 1 night some 2 so on and so forth. is there a way of showing this short of manual input?

There are a couple of other bits I need to look at too but to save my poor brain I will stick with these 2 to begin with.

I hope it makes sense if you need an example let me know.

thank you in advance

 

[AlanY]

Column E works out the unique guests, and Column F summed up the nights they stayed. Cell D2 works out no of guests

Excel 2012
	A	B	C	D	E	F
1	Guest	No of nights		No of Guest
2	Guest 5	5		5	Guest 5	31
3	Guest 5	7			Guest 1	8
4	Guest 1	1			Guest 2	13
5	Guest 2	7			Guest 4	21
6	Guest 5	2			Guest 3	11
7	Guest 5	2
8	Guest 5	7
9	Guest 5	7
10	Guest 4	4
11	Guest 5	1
12	Guest 4	1
13	Guest 3	1
14	Guest 2	6
15	Guest 4	5
16	Guest 4	6
17	Guest 3	3
18	Guest 1	6
19	Guest 1	1
20	Guest 3	7
21	Guest 4	5
Sheet7

Cell Formulas
Range	Formula
D2	=COUNTA(E:E)
F2	=SUMIF(A:A,E2,B:B)
E2	{=IFERROR(INDEX($A$2:$A$21, MATCH(0, COUNTIF(D$1:$G1, $A$2:$A$21), 0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sezebear]

Thank you AlanY, Nights is fantastic! I can't quite get my head round the guests though sorry.

 

[Sezebear]

think I have worked out how to count the 'Others' thank you