# Need Excel Sum Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### [email protected]

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?

#### Zack Barresse

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?

#### The Villan

Is the attached spreadsheet the sort of thing you are looking for?

#### Attachments

• 14 KB Views: 76

#### The Villan

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.

#### Attachments

• 16 KB Views: 69

#### bomb #21

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 ).

#### bomb #21

The Villan said:

Villan said:
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.

#### Zack Barresse

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.

#### The Villan

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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On