Excel Problem

[Ice Globe]

I'm having some difficulties creating a new formula by sourcing from a cell using the "IF" formula.

In column A, I have a list of repeating names, in column B I have a rank (1-10), and in column C I have points (using an IF formula to put rank 1 = 10 points, rank 2 = 9 points etc.)

Then I want to create a new formula for when "IF Column A name is _ _ _ _" "SUM Column C", so that only the relevant rows of the same name are totalled.

However, when I carry out the formula, the result will always be 0. I know the formula is pretty much correct, because if I replace Column C (where its trying to source the points value) with actual numbers instead of calculating it from Column B, then the correct totals are appearing.

I'm running Excel 2003, is this a problem with the version of Excel or a formula error?

Hope its not too confusing to understand and many thanks for any help!

 

[Ice Globe]

Here's an example of the data:

Surveying people's favourite drinks, asking them to list their favourite 10 in rank order. So I have a list of 10 different names per person, but obviously some if not all these names will repeat between different people.
The way I had planned was to reward a rank 1 with 10 points, and a rank 10 with 1 point, which is where my "IF" formula has been used.

Using a long string, the formula to calculate the points is:
=IF(A1=1,"10",IF(A1=2,"9",IF(A1=3,"8",.....IF(A1=10,"1")))))) which works fine and produces the expected points scores.

Now if I want to calculate the total points for each drink, as there are lots I want to use a formula:
=SUMIF(A1:A100,"Lemonade",C1:C100) which I'm hoping will find in the A column all Lemonade entries, and add the relating points score from column C together, giving me the total points value for the drink Lemonade.

Now the problem is that the result comes out as 0, with no formula errors. But I can get it to work as I would expect if I put actual numbers into the column C instead of getting excel to work out the IF formula of turning rank into points. But obviously I don't want to manually type all the rank to point calculations down the list.

 

[Ice Globe]

As said before, I am quite certain you need to remove the quotes from your IF string, so it would be:

=IF(A1=1,10,IF(A1=2,9,IF(A1=3,8,.....IF(A1=1 0,1))))))

At the moment the scores you are rewarding are character and therefore will not sum.

Edit: If this isn't the problem then I suggest saving your spreadsheet and host it somewhere so we can download and take a better look.

Apologies, missed your first reply, removing the quotes fixed it, thank you