[Solved] Summing a segment of row data

Discuss the spreadsheet application

[Solved] Summing a segment of row data

Postby Vexed » Tue May 14, 2019 1:19 pm

I have excerpted 2 rows of data: dates with their corresponding values. I want to sum the 5 values immediately following any one of the dates I select (the variable). In other words I want to sum the values for Mar 11 thru 15 when Mar 8 is the variable specified. The variable is generated elsewhere in the spreadsheet so it would be specified by a cell reference in the formula. I have experimented with “Offset” and “Sum Offset” but I am unable to plug the variable’s cell reference into a formula that does not result in an error code. What should a viable formula look like? Thanks.

Code: Select all   Expand viewCollapse view
Mar 01 Mar 04 Mar 05 Mar 06 Mar 07 Mar 08 Mar 11 Mar 12 Mar 13 Mar 14 Mar 15 Mar 18
                                 
110    -207   -13    -133   -200   -23    201    -96    148    7      139    65
Last edited by Vexed on Tue May 14, 2019 3:39 pm, edited 2 times in total.
OOo 2.4.X on Ms Windows XP
Vexed
 
Posts: 179
Joined: Sun Feb 08, 2009 1:24 am
Location: Virginia, USA

Re: Summing a segment of row data

Postby FJCC » Tue May 14, 2019 3:02 pm

I used this formula in the attached document.
Code: Select all   Expand viewCollapse view
SUM(OFFSET(B6;0;MATCH(B1;B5:M5;0);1;5))
Attachments
SumOffset.ods
(8.55 KiB) Downloaded 6 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7153
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Summing a segment of row data

Postby Vexed » Tue May 14, 2019 3:38 pm

Yep, that works. Thanks.
OOo 2.4.X on Ms Windows XP
Vexed
 
Posts: 179
Joined: Sun Feb 08, 2009 1:24 am
Location: Virginia, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests