I'm trying to define a value for individual columns only if data is present, then calculate the sum of the data in another column. I am creating this for users that have basic excel knowledge so I'm trying to only have to have them put a value of "x" in the column and have that translate into a numerical value based on what column it's in. This is for an employee score card to help with annual reviews so the column values would be -5 to 5.

Any help would be appreciated.

  • An example would go a long way to help us understand. Can you provide sample data with the desired output?
    – Abdul
    Oct 6 '17 at 10:05

How about something like this: using index() and match(). Have shown the functions separately (cells I4 & I6) and combined together (cell I2) ...

example laid out

  • Love INDEX and MATCH, probably my most used formulas, also OFFSET & MATCH can do some cool 'two' step lookups
    – PeterH
    Oct 6 '17 at 11:30
  • there are some opinions about offset() on stack overflow :) due to the calculation overhead, but it has its uses IMHO. Thanks for the feedback though - cheers
    – Solar Mike
    Oct 6 '17 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.