Sumif with Offset?

[happyhungarian]

Hi, I need a formula that will use column distance (offset) as a sumif criteria. In the image example I provide you can see that the "Sumif" row would sum up anything in Row1 where the number of columns between the data is less than or equal to the value in Row2. To illustrate, the value in cell F3 is 65 which adds together B1, D1, E1 and F1. It excludes C1 because Column F is more than "2" columns away but all others are included because their durations are less than or equal two the column distance from "F". Hope that makes sense!

 

[jputman]

Using SUMIF I think it will be difficult to achieve.

Here is a formula that uses SUMPRODUCT that I believe should get you where you want to be:

=SUMPRODUCT(DataRange*(COLUMN(DataRange)<=COLUMN())*(COLUMN(DurationRange)+DurationRange>=COLUMN()))

for this example, you would just paste the function in in all of your result cells (C3:F3)

I named the DataRange (in your example it is B1:F1) and the DurationRange (in your example, B2:F2) just to make it pretty... but feel free to use the cell addresses in their place.

Quick rundown on what is going on:`

SUMPRODUCT works with arrays of numbers... once it has the three arrays, we are multiplying the corresponding values together.
DataRange gets you the array of values to sum
(COLUMN(DataRange)<=COLUMN()) checks to see that the values are in the same column or to the left of the formula (returns 1 or 0 for each)
(COLUMN(DurationRange)+DurationRange>=COLUMN()) checks to see if the corresponding value in the duration range PLUS its column number is >= formula's column (again, returns 1,0)

Let me know if this doesn't work for you... or mark as solution, please

 

[awoohaw]

confused with your example:
sumIf for E: 10+20+15+18 = 63
sumif for F: 20+15+18+22= 75, yet you say column F should be 65?

 

[jputman]

Column F does not include column C's value of 20, but does include column B's value of 10, so...

F: 10+15+18+22 = 65

The reason he is not including column C is because the duration for that value (seen in cell C2) is 2, yet the formula for column F is 3 columns away... so his logic says not to include that value. However, he does include the value in column B because the duration for that value (seen in cell B2) is 5, so since the formula for column F is only 4 columns away, it is included.

 

[happyhungarian]

Using SUMIF I think it will be difficult to achieve.

Here is a formula that uses SUMPRODUCT that I believe should get you where you want to be:

=SUMPRODUCT(DataRange*(COLUMN(DataRange)<=COLUMN())*(COLUMN(DurationRange)+DurationRange>=COLUMN()))

for this example, you would just paste the function in in all of your result cells (C3:F3)

I named the DataRange (in your example it is B1:F1) and the DurationRange (in your example, B2:F2) just to make it pretty... but feel free to use the cell addresses in their place.

Quick rundown on what is going on:`

SUMPRODUCT works with arrays of numbers... once it has the three arrays, we are multiplying the corresponding values together.
DataRange gets you the array of values to sum
(COLUMN(DataRange)<=COLUMN()) checks to see that the values are in the same column or to the left of the formula (returns 1 or 0 for each)
(COLUMN(DurationRange)+DurationRange>=COLUMN()) checks to see if the corresponding value in the duration range PLUS its column number is >= formula's column (again, returns 1,0)

Let me know if this doesn't work for you... or mark as solution, please

Worked beautifully! Thanks!