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]

Thread Starter
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?
 
Joined
Jul 25, 2004
Messages
5,456
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?
 
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.
 

Attachments

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 :D (if you're sad like me :eek: ).
 
Joined
Jul 25, 2004
Messages
5,456
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.
 
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 :)
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top