Status
Not open for further replies.
1 - 9 of 9 Posts

#### steve-1

· Registered
Joined
·
1 Posts
Discussion Starter · ·
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

· Registered
Joined
·
5,459 Posts
Hi there,

A couple of things right off the bat:

1) Welcome to the board!
2) Please write a moderator and ask to change your username to not have the @ sign (spam bots)
3) Can you give us some examples of your data structure/layout and values?

#### The Villan

· Registered
Joined
·
2,278 Posts
Is the attached spreadsheet the sort of thing you are looking for?

#### Attachments

• 14 KB Views: 77

#### The Villan

· Registered
Joined
·
2,278 Posts
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: 70

#### bomb #21

· Registered
Joined
·
8,565 Posts
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 ).

· Registered
Joined
·
2,278 Posts
You sad git Bomb

#### bomb #21

· Registered
Joined
·
8,565 Posts
The Villan said:
You sad git Bomb

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.
:up:

#### Zack Barresse

· Registered
Joined
·
5,459 Posts
I see what you're about Villan, hard to understand the OP sometimes. LOL!

Instead of manually typing them in, how about a reverse rank of your data fields...

=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

· Registered
Joined
·
2,278 Posts
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

1 - 9 of 9 Posts
Status
Not open for further replies.