Counting Sites in Excel

[EddArmitage]

This is quite a dificult one to explain, but please bear with me.

Right, my Dad has a spreadsheet which contains an number of "Rings" (Each numbered, for example 001 - 016) in column "A". Each ring consists of up to 15 "Sites" (Each called, for example ABC001, ABC002, DEF015) arranged in columns I-->V. Some sites feature in multiple rings, however, and he wants to know how many unique sites he has. To add further complication, he has multiple versions (A, B, C etc.) of some rings, as he is currently trying to find out the most efficent method (I don't know what counts as efficent, but anyway), and he does not want to include the rings that are not being tested, so he has a column "Status", where he puts a 1 to indicate he wants that row to be included.

Here is an example (commas represent new cells):

Ring, Version, Status, Sites,

001, A, 1, ABC001, ABC002, ABC003, DEF105, DEF101,

001, B, , DEF001, DEF002, DEF003, HIJ105, HIJ101,

002, A, 1, DEF110, DEF105, ABC002, HIJ105,

In this example, the total number of sites would be 7, as ABC002 & DEF105 appear twice, and the middle line does not have status="1"

Any help here would be appreciated

Edd

[EddArmitage]

Here's a screen shot if it helps any.

I can't use actual data because of the nature of his project, and he has a lot more than this, but it is an example where the total number of sites would equal 12

Hope this helps

Edd