CODEWITHSUNDEEP
excel
Wilckens
Wilckens

Reputation: 39

Incrementing column in formula by 5 when dragging

Im trying to develop a SUMIF formula in Excel which where the "Sum_Range" column increments by five for each time i drag it. Currently, i'm trying to use the OFFSET formula, however, cannot find a way to make it increment as I want. The formula looks as follows:

=SUMIF('Customer profitability cal.'!$D:$D;'Output - Customer Profitability'!$A7;OFFSET('Customer profitability cal.'!$E:$E;0;5))

For now, the OFFSET will always just be five as I have locked column E - but even if I unlocked it, it would only increment by one for each drag.

Any ideas?

Best, Nikolaj

Upvotes: 0

Views: 165

Answers (1)

Axel Richter
Axel Richter

Reputation: 61880

If you have a sequence 0, 1, 2, 3, ... but needs 0, 5, 10, 15, ..., then do multiplying by 5: 0*5, 1*5, 2*5, 3*5, ...

The function COLUMN(A:A) returns 1. If dragged this one column to right it gets COLUMN(B:B) and returns 2. If dragged further it gets COLUMN(C:C) and returns 3. And so on. (COLUMN(A:A)-1) leads to 0, 1, 2, 3, ... if dragged to right. So (COLUMN(A:A)-1)*5 leads to 0, 5, 10, 15, ...

So for your case:

=SUMIF('Customer profitability cal.'!$D:$D,'Output - Customer Profitability'!$A7,OFFSET('Customer profitability cal.'!$E:$E,0,(COLUMN(A:A)-1)*5))

Or as with your example, using semicolon as paraneter delimiter:

=SUMIF('Customer profitability cal.'!$D:$D;'Output - Customer Profitability'!$A7;OFFSET('Customer profitability cal.'!$E:$E;0;(COLUMN(A:A)-1)*5))

Upvotes: 3

Related Questions