Thanks JBeaucaire, this is one solution I thought of, but there are a few problems:
1) The names of the widgets are not as nice as the simplistic ones I laid out in my question, they contain letters and numbers and not always in alphabetical order (which vlookup requires). I suppose I could sort the Widget list, however;
2) the number of widgets at each location could be 10-20, making the formula quite long adding them up and also requires a change to formula if a widget is added to a location.

This was the reason behind sumproduct.
I apologise for steering you in the wrong direction with my simplistic data.
Anyone other suggestions?