I have been trying to add referenced cells, but each time I input the sum formula is get 0 as an answer.

I did a search online and realized that I need to use the INDIRECT function, but I just cannot make head or tail of it. At best, I am able to retrieve one number, but my intention is not retrieval alone but adding the numbers in the referenced cells.

enter image description here

What I need to do:

  1. In I2, sum A2:D2
  2. In J2, sum lowest 2 numbers in E2:H2
  3. In K2, sum I2 and J2

Please note that the values in A2 to H2 are referenced from another sheet.

  • 1
    This question is very unclear. Maybe give us an idea of your data layout and what the actual formulas are that you've tried, along with their result vs what you expected – Raystafarian Aug 28 '16 at 11:11
  • Just make screenshot, upload it to e.g. imgur and post the link in your question. Hopefully that'll help us to understand your problem. – Máté Juhász Aug 29 '16 at 8:14
  • Thanks for the effort and suggestions so far.....Please get a screenshot here imgur.com/4zpejYs – Paa Willie Aug 29 '16 at 14:07
  • So your problem is In J2, sum lowest 2 numbers in E2:H2? Because the 1 & 3 could not pose any serious challenge, right? – Vojtěch Dohnal Aug 30 '16 at 7:23
  1. put a formula =SUM(A2:D2) into I2
  2. put a formula =SUM(SMALL(E2:H2;1); SMALL(E2:H2;2)) into J2
  3. put a formula =SUM(I2:J2) into K2

See the SMALL funtion.

Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.

If you need to translate formulas, e.g. to french, use http://en.excel-translator.de.

  • Thanks Vojtěch Dohnal, unfortunately i get a 0 for an answer using your formulas. (they were the same formulas I used initially with the same 0 as result) I have uploaded the actual excel file here (contains all formulas that I used) mediafire.com/?j51fafnacd1q9wl – Paa Willie Aug 30 '16 at 17:43
  • @PaaWillie You must convert your strings to numbers before using SMALL function. It is done using =VALUE(Sheet1!$L$11). Then use exact formula =SUM(SMALL(E2:H2;1); SMALL(E2:H2;2)), not =SUM(SMALL(E2:H2;{1\2})) – Vojtěch Dohnal Aug 31 '16 at 7:25
  • Thanks Vojtěch Donald! .......It worked!!! You have made my day! Thank you so much! – Paa Willie Aug 31 '16 at 12:02

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.