Pick List Searches

[George_Weston]

Hi!

I am trying to calculate summary information from a table (Marina) on a worksheet named Marina & Anchorage Log where I calculate the total cost of different moorings in columns.  I select the type of mooring, such as Anchorage, Marina, Yacht Club, etc., from a pick list.  The list choices are in a table LUMoorType on a different worksheet named Lookup Tables.  My current formula works OK when it searches for a specific cell (for example A4 which is "Marina") in the lookup table.  I run into a problem when I sort the lookup table or change it in any way that moves the contents of the cells around within the column.  Cell A4 may or may not be where "Marina" resides any longer.

This is an example of the current formula I am using for marina total cost.  

=SUMIF(Marina[Type],'Look up Tables'!A4,Marina[Total Cost])

I'd appreciate any suggestions for changing it to accommodate changes to my lookup table. 

Workbook is attached. 

Thanks!

George 

[#2]

Hi @George_Weston 

I am sure if you link Moorings appearing in Marina & Anchorage Log with Lookup Tables your problem will be solved because in this way data in both sheet will apprear in same order.

I have made this change in attached file, please let me know if it helps.

Thanks

Tauqeer

[#3]

@George_Weston Alternatively, in C7, change the formulae to 

=COUNTIF($B$15:$B$27,B7)

and in  C7 to

=SUMIF($B$15:$B$27,B7,$E$15:$E$27)

 replacing the reference to the list in the "Lookup tables" sheet to the first cell in the summary table on the same row. Copy these down. The summary table will keep the ordering as you desire, irrespective of the order of the list in the lookup table.