• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with SUM(INDEX) function for weekly value calculations

H

Hayley Vogg

New Member
I have a spreadsheet with a daily values tab that is calculated from a total values page. I then have a weekly values tab that adds up the a weeks worth of data from the daily values tab. For the use of this sample spreadsheet, I replaced the references in the daily output page with plain values. I do not think that this will affect the necessary function, but I wanted to disclose it just in case.

A user in a separate forum provided a function that allows me to drag the formula down the column, but when I drag the function across the row, however, it increases the increment by one, but I need to increase the increment by seven (for each week.)

The function that I am using that drags down the column but not also across the row is as follows:

=SUM(INDEX(DCM,ROW(A1),COLUMN(G1)):INDEX(DCM,ROW(A1),COLUMN(M1)))

This function is in cell B4 on the Weekly Output tab in the attached workbook. DCM is the table that is in the Daily Output tab that houses the data.
 

Attachments

Hi and welcome!

I think you're complicating it too much with such a formula. You can reach the result by simply using SUMPRODUCT (see file attached).

Best,
 

Attachments

You can use sumifs . I think TIago you got different numbers than me but your totals dont equal.

Anyway if someone knew how to get array of increasing numbers but not with columns but specifically I would be interested
 

Attachments

Thanks Tiago!

The totals for the weeks after Week 1 are not correct with the new formula though. I did notice that the calculated total for Week 3 from the new formula was the same as the actual total of week 2's data. I am assuming that isn't a coincidence. Do you know how to fix that?

Thanks for your help!
 

Attachments

Oops please excuse that last post. I got the dates of the weeks mixed up. Tiago, that formula did work out perfectly. Thank you!
 
tomas, yes sumifs can do the trick but i prefer to use sumproduct when the values for the sum are on multiple rows/columns.

Anyway, you didn't sum the same weekly ranges that me and the OP considered (e.g. your Week2 is from 24-Mar to 30-Mar while we considered from 23-Mar to 29-Mar).

On the file I sent, I purposely left the OP's formula on column B so that he could see that you can reach the same result with SUMPRODUCT (column C) with the advantage of it letting you drag the formula to the following columns.
 
Hayley

Firstly, Welcome to the Chandoo.org Forums

E24: =SUMIFS('Daily Output'!$B$24:$AH$24,'Daily Output'!$B$3:$AH$3,">="&D$3,'Daily Output'!$B$3:$AH$3,"<"&E$3)
copy sideways
 
You must log in or register to reply here.
Back
Top