CODEWITHSUNDEEP
excelexcel-formula
PortMan
PortMan

Reputation: 4523

How to add up an arbitrary number of cells

I have a spreadsheet in which for each column/category, I tally up the the values for the last X days from another sheet.

This X is different for different columns/categories.

Here is the equation that I am currently suing, which tallies up to a week's worth of values, depending on the value of D$2 (the number of days that I want to tally). (Equation has been formatted to make it easier for humans to read):

=               Daily!D15 
 + IF(D$2 >= 2, Daily!D14, 0)  
 + IF(D$2 >= 3, Daily!D13, 0) 
 + IF(D$2 >= 4, Daily!D12, 0)  
 + IF(D$2 >= 5, Daily!D11, 0) 
 + IF(D$2 >= 6, Daily!D10, 0)  
 + IF(D$2 >= 7, Daily!D9 , 0)

This works fine, as long as I only want to tally up anywhere from 1-7 days. But I would like to upgrade this spreadsheet so that it can tally an arbitrary number of of days without having to modify the equation. I'd like to be able to be able to tally, for example, 100 days without having to create an equation with 99 IF statements in it.

Upvotes: 2

Views: 442

Answers (1)

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

Offset is really handy. It returns a range of cells relative to a given reference. In your example you'd use:

=SUM(OFFSET(Daily!D15,0,0,-D$2,1))

To explain: Starting with the reference Daily!D15, we shift 0 rows and 0 columns, then select a range with D$2 rows and 1 column. Since -D$2 is negative, the range is expanded upward, rather than downward.

The last two arguments ('height' and 'width') are optional -- if left out, the range returned will have the same dimensions as the range we provided. In fact, in this case we could have used =SUM(OFFSET(Daily!D15,0,0,-D$2)), and the width of 1 column would have been implied by the width of Daily!D15. For readability, though, I like to include both dimensions or neither.

Upvotes: 3

Related Questions