[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 10 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: 7198
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: No registered users and 15 guests