# Need Excel Sum Formula

Discussion in 'Business Applications' started by [email protected]eagans.co, Oct 31, 2007.

Not open for further replies.

Joined:
Oct 31, 2007
Messages:
1
I need a spreadsheet formula that will reference an alternate numerical number in a sum function. I have a column of Priority values 1-22 and I want to assign a reverse value to each priority (i.e. 1=22, 2=21, 3=20, etc.) then I want to post the sum of the values incurred and not the sum of the numerical priority values in the cell at the bottom of the column. Should I be using LOOKUP in conjunction with SUM or SUMIF? Any ideas?

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there,

A couple of things right off the bat:

1) Welcome to the board!
3) Can you give us some examples of your data structure/layout and values?

3. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Is the attached spreadsheet the sort of thing you are looking for?

File size:
14 KB
Views:
76
4. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
I have also included how you can do subtotals of each priority using SUMIF.

This may help on understanding how to use all the functions you mentioned.

File size:
16 KB
Views:
69
5. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I don't know if Villan's suggestion is what you're after. If it is, you can get the same thing without the lookup table by using =ABS(A2-23) in B2 & copying it down. Which is kind of fun (if you're sad like me ).

Joined:
Feb 20, 2006
Messages:
2,255

Joined:
Jul 1, 2005
Messages:
8,546

8. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
I see what you're about Villan, hard to understand the OP sometimes. LOL!

=RANK(F4,\$F\$4:\$F\$25,0)

This assumes the list will be sorted and in ascending order and having each whole number in the list referenced.

9. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
I didn't type them in LOL

I typed 1 & 2 and selected them and copied down
I then typed 22 & 21 and selected them and copied them down.

actually I typed 1 & 2 in one column then 22 & 21 in the next column selected all four cells and copied down.

Just as quick me thinks

As Seen On