0

My data range is L1:L26, and its values are a mixture of numbers & strings.

L5, L8, L11, L14, L20, L23 & L26 contain numbers. The rest contain strings.

I'm trying to get the sum of the first 3 numeric values. These values are 7, 45 & 11, so the result, stored in M2, should be 63.

I'm using the following formula:

{=Sum(Sum(Offset(L1, Small(If(ISNUMBER (L2:L26), Row(L2:L26)))))} 

The formula produces an error.

NB: It's an array formula.

  • Could you also tag this question (and any others you post) with the version of Excel you are using? Some functions seem to work differently in array formulas in different Excel versions. – robinCTS Aug 23 '17 at 12:31
  • Currently I'm using Excel Version 2016.☺ – Rajesh S Aug 23 '17 at 12:43
0

This new formula definitely* works:

{=SUM(L2:INDEX(L:L,SMALL(IF(ISNUMBER(L2:L26),ROW(L2:L26)),3)))}

Just remember, this time it is entered as an array formula.


Looking at your formula, in addition to the unnecessary second SUM() there are four major errors:

  1. There is a space between the R and ( in ISNUMBER ();
  2. SMALL() is missing the required second parameter, 3;
  3. OFFSET() is missing the required third parameter, 0;
  4. The second parameter of OFFSET()is off by +1 (should be SMALL()-1).

And then there's the critical error: OFFSET() cannot be made to return an entire array*. It can only be used to fill a range when array-entered in multiple cells.


*At least in Excel 2007

  • Sorry Robin your non array formula has generated unexpected answer 212864.7 instead of 63. And if I'm using it as an array formula the answer is 7. – Rajesh S Aug 23 '17 at 9:29
  • @RajeshS Oops! Sorry about that. I tested with L14, L20, L23 and L26 set to 0 (face palm) and since my formula was actually summing all the numeric values I was getting the correct result! Updating my answer with a new working formula. – robinCTS Aug 23 '17 at 12:14
  • Finally I've got the solution and with corrections the following Formula is working perfectly. {=Sum(Sum(Offset(L1, Small(If(ISNUMBER (L2:L26), Row(L2:L26)), {1,2,3})-1,)))} @Robin if I use only one Sum formula considers only the 1st value. 2nd Sum & -1 manages the occurrence successfully. ☺ – Rajesh S Aug 24 '17 at 6:29
  • @RajeshS That's why I asked if you could tag which version of Excel you were using. Your solution doesn't work in Excel 2007. I suspect both OFFSET() and SUM() behave differently in 2007 and 2016. Just out of curiosity, does my solution work in Excel 2016? – robinCTS Aug 24 '17 at 9:08
  • ,, no your suggestion doesn't works with 2013 & 2016 also. Application software may differ in case of Function especially with VBA Codes. ☺ – Rajesh S Aug 24 '17 at 9:53

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.