count confusion - please help!

Sezebear

New Member
Joined
Nov 9, 2015
Messages
7
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?

:confused: 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. :confused:

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

thank you in advance
 

Excel Facts

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,367
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top