Correlation of data from multiple spreadsheets

Question

I am trying to collate the data from 12 spreadsheets to calculate the amount of room nights booked at certain hotels. Column E contains a booking code that I need, F contains the hotel names that I need and then I need it to add together the amount of room nights at each hotel, the room night values are in Column G. (That is if the Hilton Hotel appears once with 4 nights and then again with 6 nights, I need the answer to be 10 nights when it is correlated onto another sheet)  Any help would be great I've extended the use of my excel knowledge

Answer 1

Need to get some definitions and some other information:

"12 spreadsheets"  -- please define spreadsheet.  To some it means a single sheet in a workbook, to others it means the entire workbook (so 12 separate workbooks).  So do you mean 12 different sheets in one workbook, or 1 sheet in each of 12 other workbooks.

I'd look at either SUMIF() or, more likely, SUMIFS() formula in Excel 2010.  You'll need one formula for each worksheet - if I'm correct in assuming you meant 12 different sheets in just one workbook.

Do each of the 12 other sheets have a mixture of hotel names on them, or is each one dedicated to one hotel brand?

I think, if I understand properly, that on some sheet you could have a setup like this:

     A                         B                          C

1 Hotel                Book Code             Total Nights (assumes you must match hotel name and booking code

2 HILTON           ABCD01                =SUMIFS(Hiltons!G:G,Hiltons!F:F,A2,Hiltons!E:E,B2)

  but if you only need to match the booking code, then =SUMIF(Hiltons!E:E,B2,Hiltons!G:G)

Now, assuming hotels are mixed across the 12 sheets, then you're going to have to set up a SUMIFS() for each sheet, and add them all together, something like this for just 2 sheets would be in column C above:

=SUMIFS(ListSheet1!G:G,ListSheet1!F:F,A2,ListSheet1!E:E,B2) + SUMIFS(ListSheet2!G:G,ListSheet2!F:F,A2,ListSheet2!E:E,B2) ... etc. etc for all 12 sheets.