#### Sezebear

##### New Member
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.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### AlanY

##### Well-known Member
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
ABCDEF
1GuestNo of nightsNo of Guest
2Guest 555Guest 531
3Guest 57Guest 18
4Guest 11Guest 213
5Guest 27Guest 421
6Guest 52Guest 311
7Guest 52
8Guest 57
9Guest 57
10Guest 44
11Guest 51
12Guest 41
13Guest 31
14Guest 26
15Guest 45
16Guest 46
17Guest 33
18Guest 16
19Guest 11
20Guest 37
21Guest 45
Sheet7
Cell Formulas
RangeFormula
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

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

#### Sezebear

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

Replies
10
Views
475
Replies
8
Views
310
Replies
15
Views
244
Replies
5
Views
114
Replies
8
Views
99

1,186,647
Messages
5,958,973
Members
438,388
Latest member
wl4437

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back