+ Reply to Thread
Results 1 to 5 of 5

SUM Value in merge cell OFFSET formula issue

  1. #1
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    SUM Value in merge cell OFFSET formula issue

    Hi all,
    i need a help to to sum merge cell values with this formula =SUM(OFFSET(INDEX($B$2:$B$19,MATCH(F2,$B$2:$B$19,0)),0,2,4,1)). i want to calculate height AUTO in OFFSET rather than put manually.

    File is attached. i use a formula in column G.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUM Value in merge cell OFFSET formula issue

    Hi,

    Personally I'd avoid the volatile OFFSET if possible:

    =SUMPRODUCT(N(LOOKUP(ROW(B$2:B$19),ROW(B$2:B$19)/(B$2:B$19<>""),B$2:B$19)=F2),C$2:C$19)

    and copied down.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: SUM Value in merge cell OFFSET formula issue

    Thanks XOR LX,
    its working perfect, but as opinion OFFSET is not a suitable for this?

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: SUM Value in merge cell OFFSET formula issue

    array formulas
    Volatile
    Please Login or Register  to view this content.
    Non volatile
    Please Login or Register  to view this content.
    And extra variant
    Please Login or Register  to view this content.
    but not for use

    Not array for the case if only text value in the B column.
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Last edited by BMV; 04-28-2020 at 12:09 PM.

  5. #5
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: SUM Value in merge cell OFFSET formula issue

    Thanks a lot BOTH.
    its perfect, cheers

    Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Issue with Offset/ Match formula that references a variable row and cell
    By Grilleman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2020, 08:25 PM
  2. how to use Offset in array formula to avoid #ref issue
    By barman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2019, 03:16 PM
  3. sum and offset formula issue
    By jw01 in forum Excel General
    Replies: 3
    Last Post: 02-21-2019, 11:24 AM
  4. Dynamic formula with offset and match Issue please help.
    By ebitda_traps in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-04-2019, 06:44 PM
  5. Offset(Match Formula Issue
    By maske002 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2018, 12:53 PM
  6. sumifs + offset formula issue
    By jw01 in forum Excel General
    Replies: 6
    Last Post: 02-11-2015, 11:05 AM
  7. how to merge workbooks? formula issue
    By rvanzo in forum Excel General
    Replies: 1
    Last Post: 03-01-2011, 10:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1