Ranking Potential Business Partners

**lukeaf** · 08-21-2012, 03:34 PM

Hello,

I'm trying to develop a sales spreadsheet that helps me to analyze potential business partners based on certain criteria.

Where I'm getting held up is that some of the criteria have multiple options and I would like to be able to select more than one option and have a value associate with each option.

For instance:

One of the criteria for ranking is Geography.
If a partner has locations in multiple geographies (ex. APAC, EU, NA, etc.) I want to be able to select all of their current geographies.
However, some geographies are valued more than others. I would value NA = 3 whereas APAC = 1.

How do I assign these values to each geography individually but be able to select multiple geographies in one cell that ultimately lead to a final value that I can appropriately judge a potential partner?

I hope this makes sense.

I appreciate your responses in advanced.

**tlafferty** · 08-21-2012, 03:51 PM

Enter each geography in the first column of a table.
Enter the values you want to assign to each geography in the second column of a table.

use a vlookup on the name with error suppression.
Perform a row sum.

See attached.

**tlafferty** · 08-21-2012, 03:52 PM

Easier to maintain, and much cooler: use a pivot table. If you post a sample, I can show you how to do this.

**lukeaf** · 08-21-2012, 04:37 PM

Here is an example of my spreadsheet.

You'll notice the criteria across the top, I'm trying to rank based on each of those criteria. Then, I would like to have a
total rank at the end of each row, reflecting the score each company received.