1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need Excel Sum Formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. steve@reagans.co

    [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?
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  3. The Villan

    The Villan

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

    Attached Files:

  4. The Villan

    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.
     

    Attached Files:

  5. bomb #21

    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 :D (if you're sad like me :eek: ).
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    You sad git Bomb :D :D
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    :D

    (y)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  9. The Villan

    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 :)
     
  10. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/646101

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice